﻿ 
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__AdvanceSe__Emplo__0B7CAB7B]') AND parent_object_id = OBJECT_ID(N'[dbo].[AdvanceSearch]'))
ALTER TABLE [dbo].[AdvanceSearch] DROP CONSTRAINT [FK__AdvanceSe__Emplo__0B7CAB7B]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__BD__BGID__0C70CFB4]') AND parent_object_id = OBJECT_ID(N'[dbo].[BD]'))
ALTER TABLE [dbo].[BD] DROP CONSTRAINT [FK__BD__BGID__0C70CFB4]
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_BD_Active]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[BD] DROP CONSTRAINT [DF_BD_Active]
END

GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__BDAssignm__Emplo__0E591826]') AND parent_object_id = OBJECT_ID(N'[dbo].[BDAssignment]'))
ALTER TABLE [dbo].[BDAssignment] DROP CONSTRAINT [FK__BDAssignm__Emplo__0E591826]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__BDAssignme__BDID__0D64F3ED]') AND parent_object_id = OBJECT_ID(N'[dbo].[BDAssignment]'))
ALTER TABLE [dbo].[BDAssignment] DROP CONSTRAINT [FK__BDAssignme__BDID__0D64F3ED]
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_BG_Active]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[BG] DROP CONSTRAINT [DF_BG_Active]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_BG_CreateDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[BG] DROP CONSTRAINT [DF_BG_CreateDate]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_BG_UpdateDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[BG] DROP CONSTRAINT [DF_BG_UpdateDate]
END

GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__BGAssignm__Emplo__10416098]') AND parent_object_id = OBJECT_ID(N'[dbo].[BGAssignment]'))
ALTER TABLE [dbo].[BGAssignment] DROP CONSTRAINT [FK__BGAssignm__Emplo__10416098]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__BGAssignme__BGID__0F4D3C5F]') AND parent_object_id = OBJECT_ID(N'[dbo].[BGAssignment]'))
ALTER TABLE [dbo].[BGAssignment] DROP CONSTRAINT [FK__BGAssignme__BGID__0F4D3C5F]
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_CAT_Active]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[CAT] DROP CONSTRAINT [DF_CAT_Active]
END

GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CATAssignment_CAT]') AND parent_object_id = OBJECT_ID(N'[dbo].[CATAssignment]'))
ALTER TABLE [dbo].[CATAssignment] DROP CONSTRAINT [FK_CATAssignment_CAT]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CATAssignment_Employee]') AND parent_object_id = OBJECT_ID(N'[dbo].[CATAssignment]'))
ALTER TABLE [dbo].[CATAssignment] DROP CONSTRAINT [FK_CATAssignment_Employee]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__ContractD__PreCo__131DCD43]') AND parent_object_id = OBJECT_ID(N'[dbo].[ContractDelivery]'))
ALTER TABLE [dbo].[ContractDelivery] DROP CONSTRAINT [FK__ContractD__PreCo__131DCD43]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__ContractT__PreCo__1411F17C]') AND parent_object_id = OBJECT_ID(N'[dbo].[ContractTransfer]'))
ALTER TABLE [dbo].[ContractTransfer] DROP CONSTRAINT [FK__ContractT__PreCo__1411F17C]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__DicItem__DicType__150615B5]') AND parent_object_id = OBJECT_ID(N'[dbo].[DicItem]'))
ALTER TABLE [dbo].[DicItem] DROP CONSTRAINT [FK__DicItem__DicType__150615B5]
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_DicItem_IsEnabled]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[DicItem] DROP CONSTRAINT [DF_DicItem_IsEnabled]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_DicType_IsSystem]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[DicType] DROP CONSTRAINT [DF_DicType_IsSystem]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_DicType_IsEnabled]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[DicType] DROP CONSTRAINT [DF_DicType_IsEnabled]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__Email__IsSend__7C8480AE]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Email] DROP CONSTRAINT [DF__Email__IsSend__7C8480AE]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Employee_Active]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [DF_Employee_Active]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Employee_CommentAllowed]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [DF_Employee_CommentAllowed]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Employee_ASBOrLucent]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [DF_Employee_ASBOrLucent]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Employee_DownloadAllowed]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [DF_Employee_DownloadAllowed]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_ExchangeRate_UpdateDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[ExchangeRate] DROP CONSTRAINT [DF_ExchangeRate_UpdateDate]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_HelpDoc_CreatedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[HelpDoc] DROP CONSTRAINT [DF_HelpDoc_CreatedDate]
END

GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_MenuRoleMapping_MenuConfig]') AND parent_object_id = OBJECT_ID(N'[dbo].[MenuRoleMapping]'))
ALTER TABLE [dbo].[MenuRoleMapping] DROP CONSTRAINT [FK_MenuRoleMapping_MenuConfig]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_MenuRoleMapping_Role]') AND parent_object_id = OBJECT_ID(N'[dbo].[MenuRoleMapping]'))
ALTER TABLE [dbo].[MenuRoleMapping] DROP CONSTRAINT [FK_MenuRoleMapping_Role]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_PreContractInfo_BD]') AND parent_object_id = OBJECT_ID(N'[dbo].[PreContractInfo]'))
ALTER TABLE [dbo].[PreContractInfo] DROP CONSTRAINT [FK_PreContractInfo_BD]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_PreContractInfo_CAT]') AND parent_object_id = OBJECT_ID(N'[dbo].[PreContractInfo]'))
ALTER TABLE [dbo].[PreContractInfo] DROP CONSTRAINT [FK_PreContractInfo_CAT]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_PreContractInfo_DicItem]') AND parent_object_id = OBJECT_ID(N'[dbo].[PreContractInfo]'))
ALTER TABLE [dbo].[PreContractInfo] DROP CONSTRAINT [FK_PreContractInfo_DicItem]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_PreContractInfo_DicItem1]') AND parent_object_id = OBJECT_ID(N'[dbo].[PreContractInfo]'))
ALTER TABLE [dbo].[PreContractInfo] DROP CONSTRAINT [FK_PreContractInfo_DicItem1]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_PreContractInfo_Product]') AND parent_object_id = OBJECT_ID(N'[dbo].[PreContractInfo]'))
ALTER TABLE [dbo].[PreContractInfo] DROP CONSTRAINT [FK_PreContractInfo_Product]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_PreContractInfo_Province]') AND parent_object_id = OBJECT_ID(N'[dbo].[PreContractInfo]'))
ALTER TABLE [dbo].[PreContractInfo] DROP CONSTRAINT [FK_PreContractInfo_Province]
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_PreContractInfo_Delivered]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[PreContractInfo] DROP CONSTRAINT [DF_PreContractInfo_Delivered]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_PreContractInfo_IsRisk]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[PreContractInfo] DROP CONSTRAINT [DF_PreContractInfo_IsRisk]
END

GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_PreContractInfoComment_Employee]') AND parent_object_id = OBJECT_ID(N'[dbo].[PreContractInfoComment]'))
ALTER TABLE [dbo].[PreContractInfoComment] DROP CONSTRAINT [FK_PreContractInfoComment_Employee]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_PreContractInfoComment_Employee1]') AND parent_object_id = OBJECT_ID(N'[dbo].[PreContractInfoComment]'))
ALTER TABLE [dbo].[PreContractInfoComment] DROP CONSTRAINT [FK_PreContractInfoComment_Employee1]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_PreContractInfoComment_PreContractInfo]') AND parent_object_id = OBJECT_ID(N'[dbo].[PreContractInfoComment]'))
ALTER TABLE [dbo].[PreContractInfoComment] DROP CONSTRAINT [FK_PreContractInfoComment_PreContractInfo]
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_PreContractInfoComment_DateCreated]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[PreContractInfoComment] DROP CONSTRAINT [DF_PreContractInfoComment_DateCreated]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_PreContractInfoComment_DateModified]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[PreContractInfoComment] DROP CONSTRAINT [DF_PreContractInfoComment_DateModified]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_PreContractInfoComment_IsRisk]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[PreContractInfoComment] DROP CONSTRAINT [DF_PreContractInfoComment_IsRisk]
END

GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Product_DicItem]') AND parent_object_id = OBJECT_ID(N'[dbo].[Product]'))
ALTER TABLE [dbo].[Product] DROP CONSTRAINT [FK_Product_DicItem]
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Province_IsEnabled]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Province] DROP CONSTRAINT [DF_Province_IsEnabled]
END

GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__ProvinceA__Emplo__1F83A428]') AND parent_object_id = OBJECT_ID(N'[dbo].[ProvinceAssignment]'))
ALTER TABLE [dbo].[ProvinceAssignment] DROP CONSTRAINT [FK__ProvinceA__Emplo__1F83A428]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__ProvinceA__Provi__2077C861]') AND parent_object_id = OBJECT_ID(N'[dbo].[ProvinceAssignment]'))
ALTER TABLE [dbo].[ProvinceAssignment] DROP CONSTRAINT [FK__ProvinceA__Provi__2077C861]
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_TempContractList_UpdateDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[TempContractList] DROP CONSTRAINT [DF_TempContractList_UpdateDate]
END

GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__UserRole__Employ__216BEC9A]') AND parent_object_id = OBJECT_ID(N'[dbo].[UserRole]'))
ALTER TABLE [dbo].[UserRole] DROP CONSTRAINT [FK__UserRole__Employ__216BEC9A]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__UserRole__RoleID__226010D3]') AND parent_object_id = OBJECT_ID(N'[dbo].[UserRole]'))
ALTER TABLE [dbo].[UserRole] DROP CONSTRAINT [FK__UserRole__RoleID__226010D3]
GO

USE [PreContract]
GO

/****** Object:  Table [dbo].[AdvanceSearch]    Script Date: 07/03/2009 13:53:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AdvanceSearch]') AND type in (N'U'))
DROP TABLE [dbo].[AdvanceSearch]
GO

/****** Object:  Table [dbo].[BD]    Script Date: 07/03/2009 13:53:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BD]') AND type in (N'U'))
DROP TABLE [dbo].[BD]
GO

/****** Object:  Table [dbo].[BDAssignment]    Script Date: 07/03/2009 13:53:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BDAssignment]') AND type in (N'U'))
DROP TABLE [dbo].[BDAssignment]
GO

/****** Object:  Table [dbo].[BG]    Script Date: 07/03/2009 13:53:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BG]') AND type in (N'U'))
DROP TABLE [dbo].[BG]
GO

/****** Object:  Table [dbo].[BGAssignment]    Script Date: 07/03/2009 13:53:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BGAssignment]') AND type in (N'U'))
DROP TABLE [dbo].[BGAssignment]
GO

/****** Object:  Table [dbo].[CAT]    Script Date: 07/03/2009 13:53:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CAT]') AND type in (N'U'))
DROP TABLE [dbo].[CAT]
GO

/****** Object:  Table [dbo].[CATAssignment]    Script Date: 07/03/2009 13:53:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CATAssignment]') AND type in (N'U'))
DROP TABLE [dbo].[CATAssignment]
GO

/****** Object:  Table [dbo].[CloseDate]    Script Date: 07/03/2009 13:53:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CloseDate]') AND type in (N'U'))
DROP TABLE [dbo].[CloseDate]
GO

/****** Object:  Table [dbo].[ContractDelivery]    Script Date: 07/03/2009 13:53:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ContractDelivery]') AND type in (N'U'))
DROP TABLE [dbo].[ContractDelivery]
GO

/****** Object:  Table [dbo].[ContractTransfer]    Script Date: 07/03/2009 13:53:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ContractTransfer]') AND type in (N'U'))
DROP TABLE [dbo].[ContractTransfer]
GO

/****** Object:  Table [dbo].[Deadline]    Script Date: 07/03/2009 13:53:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Deadline]') AND type in (N'U'))
DROP TABLE [dbo].[Deadline]
GO

/****** Object:  Table [dbo].[DicItem]    Script Date: 07/03/2009 13:53:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DicItem]') AND type in (N'U'))
DROP TABLE [dbo].[DicItem]
GO

/****** Object:  Table [dbo].[DicType]    Script Date: 07/03/2009 13:53:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DicType]') AND type in (N'U'))
DROP TABLE [dbo].[DicType]
GO

/****** Object:  Table [dbo].[Email]    Script Date: 07/03/2009 13:53:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Email]') AND type in (N'U'))
DROP TABLE [dbo].[Email]
GO

/****** Object:  Table [dbo].[Employee]    Script Date: 07/03/2009 13:53:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Employee]') AND type in (N'U'))
DROP TABLE [dbo].[Employee]
GO

/****** Object:  Table [dbo].[ExchangeRate]    Script Date: 07/03/2009 13:53:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ExchangeRate]') AND type in (N'U'))
DROP TABLE [dbo].[ExchangeRate]
GO

/****** Object:  Table [dbo].[HelpDoc]    Script Date: 07/03/2009 13:53:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HelpDoc]') AND type in (N'U'))
DROP TABLE [dbo].[HelpDoc]
GO

/****** Object:  Table [dbo].[KeyProjectDefaultAmount]    Script Date: 07/03/2009 13:53:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[KeyProjectDefaultAmount]') AND type in (N'U'))
DROP TABLE [dbo].[KeyProjectDefaultAmount]
GO

/****** Object:  Table [dbo].[MenuConfig]    Script Date: 07/03/2009 13:53:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MenuConfig]') AND type in (N'U'))
DROP TABLE [dbo].[MenuConfig]
GO

/****** Object:  Table [dbo].[MenuRoleMapping]    Script Date: 07/03/2009 13:53:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MenuRoleMapping]') AND type in (N'U'))
DROP TABLE [dbo].[MenuRoleMapping]
GO

/****** Object:  Table [dbo].[OperationLog]    Script Date: 07/03/2009 13:53:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OperationLog]') AND type in (N'U'))
DROP TABLE [dbo].[OperationLog]
GO

/****** Object:  Table [dbo].[PreContractInfo]    Script Date: 07/03/2009 13:53:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PreContractInfo]') AND type in (N'U'))
DROP TABLE [dbo].[PreContractInfo]
GO

/****** Object:  Table [dbo].[PreContractInfoComment]    Script Date: 07/03/2009 13:53:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PreContractInfoComment]') AND type in (N'U'))
DROP TABLE [dbo].[PreContractInfoComment]
GO

/****** Object:  Table [dbo].[Product]    Script Date: 07/03/2009 13:53:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Product]') AND type in (N'U'))
DROP TABLE [dbo].[Product]
GO

/****** Object:  Table [dbo].[Province]    Script Date: 07/03/2009 13:53:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Province]') AND type in (N'U'))
DROP TABLE [dbo].[Province]
GO

/****** Object:  Table [dbo].[ProvinceAssignment]    Script Date: 07/03/2009 13:53:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProvinceAssignment]') AND type in (N'U'))
DROP TABLE [dbo].[ProvinceAssignment]
GO

/****** Object:  Table [dbo].[ReportHistory]    Script Date: 07/03/2009 13:53:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ReportHistory]') AND type in (N'U'))
DROP TABLE [dbo].[ReportHistory]
GO

/****** Object:  Table [dbo].[ReportSearchDefaultMonth]    Script Date: 07/03/2009 13:53:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ReportSearchDefaultMonth]') AND type in (N'U'))
DROP TABLE [dbo].[ReportSearchDefaultMonth]
GO

/****** Object:  Table [dbo].[Role]    Script Date: 07/03/2009 13:53:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Role]') AND type in (N'U'))
DROP TABLE [dbo].[Role]
GO

/****** Object:  Table [dbo].[SysRefDate]    Script Date: 07/03/2009 13:53:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SysRefDate]') AND type in (N'U'))
DROP TABLE [dbo].[SysRefDate]
GO

/****** Object:  Table [dbo].[TempContractList]    Script Date: 07/03/2009 13:53:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TempContractList]') AND type in (N'U'))
DROP TABLE [dbo].[TempContractList]
GO

/****** Object:  Table [dbo].[UserRole]    Script Date: 07/03/2009 13:53:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserRole]') AND type in (N'U'))
DROP TABLE [dbo].[UserRole]
GO


/****** Object:  Table [dbo].[AdvanceSearch]    Script Date: 07/03/2009 13:53:41 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING OFF
GO

CREATE TABLE [dbo].[AdvanceSearch](
	[SearchID] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeID] [int] NOT NULL,
	[SearchType] [nvarchar](20) NOT NULL,
	[SearchName] [nvarchar](50) NOT NULL,
	[Description] [nvarchar](2000) NULL,
	[SearchSQL] [varchar](8000) NULL,
	[SearchCondition] [varchar](8000) NULL,
	[SearchField] [varchar](8000) NULL,
PRIMARY KEY CLUSTERED 
(
	[SearchID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

USE [PreContract]
GO

/****** Object:  Table [dbo].[BD]    Script Date: 07/03/2009 13:53:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[BD](
	[BDID] [int] IDENTITY(1,1) NOT NULL,
	[BDCode] [nvarchar](15) NOT NULL,
	[SortNumber] [int] NULL,
	[CreateDate] [smalldatetime] NOT NULL,
	[Active] [bit] NOT NULL,
	[Division] [nvarchar](50) NULL,
	[BGID] [int] NOT NULL,
	[UpdateDate] [smalldatetime] NULL,
	[BDDescription] [nvarchar](50) NULL,
 CONSTRAINT [PK__BD__60924D76] PRIMARY KEY CLUSTERED 
(
	[BDID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [PreContract]
GO

/****** Object:  Table [dbo].[BDAssignment]    Script Date: 07/03/2009 13:53:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[BDAssignment](
	[BDAssignmentID] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeID] [int] NOT NULL,
	[BDID] [int] NOT NULL,
 CONSTRAINT [PK__BDAssignment__627A95E8] PRIMARY KEY CLUSTERED 
(
	[BDAssignmentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [PreContract]
GO

/****** Object:  Table [dbo].[BG]    Script Date: 07/03/2009 13:53:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[BG](
	[BGID] [int] IDENTITY(1,1) NOT NULL,
	[BGCode] [nvarchar](15) NOT NULL,
	[SortNumber] [int] NULL,
	[Active] [bit] NOT NULL,
	[CreateDate] [datetime] NOT NULL,
	[UpdateDate] [datetime] NULL,
 CONSTRAINT [PK__BG__6462DE5A] PRIMARY KEY CLUSTERED 
(
	[BGID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [PreContract]
GO

/****** Object:  Table [dbo].[BGAssignment]    Script Date: 07/03/2009 13:53:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[BGAssignment](
	[BGAssignmentID] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeID] [int] NOT NULL,
	[BGID] [int] NOT NULL,
 CONSTRAINT [PK__BGAssignment__664B26CC] PRIMARY KEY CLUSTERED 
(
	[BGAssignmentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [PreContract]
GO

/****** Object:  Table [dbo].[CAT]    Script Date: 07/03/2009 13:53:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[CAT](
	[CATID] [int] IDENTITY(1,1) NOT NULL,
	[CATCode] [nvarchar](10) NOT NULL,
	[CATCode_Shortening] [char](10) NOT NULL,
	[CATName] [nvarchar](50) NOT NULL,
	[SortNumber] [int] NOT NULL,
	[Active] [bit] NOT NULL,
	[CreateDate] [datetime] NULL,
	[UpdateDate] [datetime] NULL,
 CONSTRAINT [PK__CAT__68336F3E] PRIMARY KEY CLUSTERED 
(
	[CATID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

USE [PreContract]
GO

/****** Object:  Table [dbo].[CATAssignment]    Script Date: 07/03/2009 13:53:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CATAssignment](
	[CATAssignmentID] [int] IDENTITY(1,1) NOT NULL,
	[CATID] [int] NOT NULL,
	[EmployeeID] [int] NOT NULL,
 CONSTRAINT [PK__CATAssignment__6A1BB7B0] PRIMARY KEY CLUSTERED 
(
	[CATAssignmentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [PreContract]
GO

/****** Object:  Table [dbo].[CloseDate]    Script Date: 07/03/2009 13:53:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CloseDate](
	[CloseDateID] [int] IDENTITY(1,1) NOT NULL,
	[Year] [int] NOT NULL,
	[Month] [int] NOT NULL,
	[Quarter] [int] NOT NULL,
	[StartDate] [smalldatetime] NOT NULL,
	[EndDate] [smalldatetime] NOT NULL,
 CONSTRAINT [PK__CloseDate__6C040022] PRIMARY KEY CLUSTERED 
(
	[CloseDateID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [PreContract]
GO

/****** Object:  Table [dbo].[ContractDelivery]    Script Date: 07/03/2009 13:53:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ContractDelivery](
	[ContractDeliveryID] [int] IDENTITY(1,1) NOT NULL,
	[PreContractID] [int] NOT NULL,
	[Percentage] [decimal](5, 2) NOT NULL,
	[UpdateDate] [smalldatetime] NOT NULL,
	[Comments] [nvarchar](1000) NULL,
 CONSTRAINT [PK__ContractDelivery__6DEC4894] PRIMARY KEY CLUSTERED 
(
	[ContractDeliveryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [PreContract]
GO

/****** Object:  Table [dbo].[ContractTransfer]    Script Date: 07/03/2009 13:53:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ContractTransfer](
	[ContractTransferID] [int] IDENTITY(1,1) NOT NULL,
	[TransferDate] [smalldatetime] NOT NULL,
	[TransferMoney] [float] NOT NULL,
	[ContractNo] [nvarchar](20) NOT NULL,
	[PreContractID] [int] NOT NULL,
	[UpdateDate] [smalldatetime] NOT NULL,
	[TransferType] [nvarchar](50) NULL,
	[RemainMoney] [float] NULL,
 CONSTRAINT [PK__ContractTransfer__71BCD978] PRIMARY KEY CLUSTERED 
(
	[ContractTransferID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [PreContract]
GO

/****** Object:  Table [dbo].[Deadline]    Script Date: 07/03/2009 13:53:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Deadline](
	[DeadlineID] [int] IDENTITY(1,1) NOT NULL,
	[DeadlineForData] [smalldatetime] NOT NULL,
 CONSTRAINT [PK_UpToDate] PRIMARY KEY CLUSTERED 
(
	[DeadlineID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [PreContract]
GO

/****** Object:  Table [dbo].[DicItem]    Script Date: 07/03/2009 13:53:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[DicItem](
	[DicItemID] [int] IDENTITY(1,1) NOT NULL,
	[DicTypeID] [int] NOT NULL,
	[DicItemValue] [nvarchar](100) NOT NULL,
	[DicItemDesc] [nvarchar](100) NULL,
	[IsEnabled] [bit] NOT NULL,
 CONSTRAINT [PK__DicItem__758D6A5C] PRIMARY KEY CLUSTERED 
(
	[DicItemID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [PreContract]
GO

/****** Object:  Table [dbo].[DicType]    Script Date: 07/03/2009 13:53:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[DicType](
	[DicTypeID] [int] IDENTITY(1,1) NOT NULL,
	[DicTypeValue] [nvarchar](50) NOT NULL,
	[DicTypeDesc] [nvarchar](100) NULL,
	[IsSystem] [bit] NOT NULL,
	[IsEnabled] [bit] NOT NULL,
 CONSTRAINT [PK__DicType__7775B2CE] PRIMARY KEY CLUSTERED 
(
	[DicTypeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [PreContract]
GO

/****** Object:  Table [dbo].[Email]    Script Date: 07/03/2009 13:53:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Email](
	[EmailID] [int] IDENTITY(1,1) NOT NULL,
	[ClassName] [varchar](255) NULL,
	[PreContractID] [int] NULL,
	[EmailContent] [varchar](6000) NULL,
	[CC] [nvarchar](50) NULL,
	[Subject] [nvarchar](200) NULL,
	[IsSend] [bit] NOT NULL,
	[SendTime] [datetime] NULL,
 CONSTRAINT [Email_PK] PRIMARY KEY CLUSTERED 
(
	[EmailID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

USE [PreContract]
GO

/****** Object:  Table [dbo].[Employee]    Script Date: 07/03/2009 13:53:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Employee](
	[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
	[Badge] [nvarchar](50) NOT NULL,
	[EName] [nvarchar](50) NOT NULL,
	[CName] [nvarchar](50) NULL,
	[Email] [nvarchar](50) NULL,
	[CILAccount] [nvarchar](50) NOT NULL,
	[BD] [nvarchar](50) NOT NULL,
	[BG] [nvarchar](50) NOT NULL,
	[Title] [nvarchar](200) NULL,
	[Culture] [nvarchar](50) NULL,
	[ReportTo] [nvarchar](50) NULL,
	[Active] [bit] NOT NULL,
	[UpdateDate] [smalldatetime] NOT NULL,
	[CommentAllowed] [bit] NOT NULL,
	[ASBOrLucent] [int] NOT NULL,
	[DownloadAllowed] [bit] NOT NULL,
	[Phone] [nvarchar](50) NULL,
	[Company] [nvarchar](50) NULL,
	[Location] [nvarchar](50) NULL,
	[ADAccount] [nvarchar](50) NULL,
 CONSTRAINT [PK__Employee__795DFB40] PRIMARY KEY CLUSTERED 
(
	[EmployeeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'0-Consolidated,1-ASB, 2-Lucent' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'ASBOrLucent'
GO

USE [PreContract]
GO

/****** Object:  Table [dbo].[ExchangeRate]    Script Date: 07/03/2009 13:53:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ExchangeRate](
	[ExchangeRateID] [int] IDENTITY(1,1) NOT NULL,
	[Rate] [decimal](8, 6) NOT NULL,
	[UpdateDate] [smalldatetime] NOT NULL,
 CONSTRAINT [PK__ExchangeRate__7B4643B2] PRIMARY KEY CLUSTERED 
(
	[ExchangeRateID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [PreContract]
GO

/****** Object:  Table [dbo].[HelpDoc]    Script Date: 07/03/2009 13:53:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[HelpDoc](
	[DocID] [int] IDENTITY(1,1) NOT NULL,
	[DocDescription] [nvarchar](1000) NOT NULL,
	[DocPath] [nvarchar](500) NOT NULL,
	[DateCreated] [smalldatetime] NOT NULL,
	[UploadedBy] [int] NULL,
 CONSTRAINT [PK_HelpDoc] PRIMARY KEY CLUSTERED 
(
	[DocID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [PreContract]
GO

/****** Object:  Table [dbo].[KeyProjectDefaultAmount]    Script Date: 07/03/2009 13:53:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[KeyProjectDefaultAmount](
	[KeyProjectDefaultAmountID] [int] IDENTITY(1,1) NOT NULL,
	[DefaultAmount] [money] NOT NULL,
	[UpdateDate] [smalldatetime] NOT NULL,
 CONSTRAINT [PK_KeyProjectDefaultAmount] PRIMARY KEY CLUSTERED 
(
	[KeyProjectDefaultAmountID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [PreContract]
GO

/****** Object:  Table [dbo].[MenuConfig]    Script Date: 07/03/2009 13:53:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[MenuConfig](
	[MenuID] [int] IDENTITY(1,1) NOT NULL,
	[MenuTitle] [varchar](200) NOT NULL,
	[MenuTitle_CN] [nvarchar](500) NULL,
	[MenuURL] [varchar](200) NULL,
	[ParentMenuID] [int] NULL,
	[MenuSequenceID] [int] NULL,
	[IsEnabled] [bit] NOT NULL,
	[Target] [varchar](50) NOT NULL,
	[MenuGroup] [varchar](100) NULL,
 CONSTRAINT [PK_MenuConfig] PRIMARY KEY CLUSTERED 
(
	[MenuID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

USE [PreContract]
GO

/****** Object:  Table [dbo].[MenuRoleMapping]    Script Date: 07/03/2009 13:53:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[MenuRoleMapping](
	[MenuRoleID] [int] IDENTITY(1,1) NOT NULL,
	[MenuID] [int] NOT NULL,
	[RoleID] [int] NOT NULL,
 CONSTRAINT [PK_MenuRoleMapping] PRIMARY KEY CLUSTERED 
(
	[MenuRoleID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [PreContract]
GO

/****** Object:  Table [dbo].[OperationLog]    Script Date: 07/03/2009 13:53:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[OperationLog](
	[OperationLogID] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeName] [nvarchar](50) NOT NULL,
	[OperationType] [nvarchar](50) NOT NULL,
	[LogContent] [nvarchar](4000) NULL,
	[OperationDate] [datetime] NOT NULL,
	[HostName] [nvarchar](100) NOT NULL,
	[HostIP] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_OperationLog] PRIMARY KEY CLUSTERED 
(
	[OperationLogID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [PreContract]
GO

/****** Object:  Table [dbo].[PreContractInfo]    Script Date: 07/03/2009 13:53:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[PreContractInfo](
	[PreContractID] [int] IDENTITY(1,1) NOT NULL,
	[PreContractNo] [nvarchar](20) NOT NULL,
	[StatusID] [int] NOT NULL,
	[TypeID] [int] NOT NULL,
	[ProductID] [int] NOT NULL,
	[PromisedDate] [smalldatetime] NULL,
	[CEWPromised] [money] NOT NULL,
	[OrionPrice] [money] NOT NULL,
	[SalePrice] [money] NULL,
	[SignDue] [smalldatetime] NULL,
	[SignActual] [smalldatetime] NULL,
	[ContractName] [nvarchar](200) NULL,
	[ERPReceived] [smalldatetime] NULL,
	[OrionNo] [nvarchar](200) NULL,
	[Delivered] [bit] NOT NULL,
	[CurrentSituation] [nvarchar](1000) NULL,
	[BlackDate] [smalldatetime] NULL,
	[TradeMonth] [smalldatetime] NULL,
	[ModifiedDate] [smalldatetime] NULL,
	[ACRNo] [nvarchar](20) NULL,
	[ASBorLucent] [bit] NOT NULL,
	[Applicant] [nvarchar](20) NULL,
	[ApplicantEmail] [nvarchar](100) NULL,
	[Principal] [nvarchar](20) NULL,
	[PrincipalEmail] [nvarchar](100) NULL,
	[ProvinceID] [int] NOT NULL,
	[CATID] [int] NOT NULL,
	[BDID] [int] NOT NULL,
	[CustomerName] [nvarchar](200) NULL,
	[CustomField01] [nvarchar](500) NULL,
	[CustomField02] [nvarchar](500) NULL,
	[CustomField03] [nvarchar](500) NULL,
	[CustomField04] [nvarchar](500) NULL,
	[DeliveryPercent] [decimal](5, 2) NULL,
	[AdvancedContract] [nvarchar](500) NULL,
	[IsRisk] [bit] NOT NULL,
 CONSTRAINT [PK__PreContractInfo__000AF8CF] PRIMARY KEY CLUSTERED 
(
	[PreContractID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [PreContract]
GO

/****** Object:  Table [dbo].[PreContractInfoComment]    Script Date: 07/03/2009 13:53:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[PreContractInfoComment](
	[PreContractInfoCommentID] [int] IDENTITY(1,1) NOT NULL,
	[PreContractID] [int] NOT NULL,
	[Comment] [nvarchar](2000) NOT NULL,
	[Creator] [int] NOT NULL,
	[DateCreated] [datetime] NOT NULL,
	[ModifiedBy] [int] NOT NULL,
	[DateModified] [datetime] NOT NULL,
	[IsRisk] [bit] NULL,
	[CommentPromisedDate] [datetime] NULL,
 CONSTRAINT [PK_PreContractInfoComment] PRIMARY KEY CLUSTERED 
(
	[PreContractInfoCommentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [PreContract]
GO

/****** Object:  Table [dbo].[Product]    Script Date: 07/03/2009 13:53:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Product](
	[ProductID] [int] IDENTITY(1,1) NOT NULL,
	[ProductName] [nvarchar](200) NOT NULL,
	[Description] [nvarchar](500) NULL,
	[Quantity] [numeric](18, 4) NULL,
	[UnitID] [int] NULL,
 CONSTRAINT [PK__Product__01F34141] PRIMARY KEY CLUSTERED 
(
	[ProductID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [PreContract]
GO

/****** Object:  Table [dbo].[Province]    Script Date: 07/03/2009 13:53:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Province](
	[ProvinceID] [int] IDENTITY(1,1) NOT NULL,
	[ProvinceCode] [nvarchar](50) NOT NULL,
	[ProvinceEName] [nvarchar](100) NOT NULL,
	[ProvinceCName] [nvarchar](20) NOT NULL,
	[SortNumber] [int] NOT NULL,
	[CreateDate] [smalldatetime] NOT NULL,
	[UpdateDate] [smalldatetime] NOT NULL,
	[Active] [bit] NOT NULL,
 CONSTRAINT [PK__Province__05C3D225] PRIMARY KEY CLUSTERED 
(
	[ProvinceID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [PreContract]
GO

/****** Object:  Table [dbo].[ProvinceAssignment]    Script Date: 07/03/2009 13:53:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ProvinceAssignment](
	[ProvinceAssignmentID] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeID] [int] NOT NULL,
	[ProvinceID] [int] NOT NULL,
 CONSTRAINT [PK__ProvinceAssignme__07AC1A97] PRIMARY KEY CLUSTERED 
(
	[ProvinceAssignmentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [PreContract]
GO

/****** Object:  Table [dbo].[ReportHistory]    Script Date: 07/03/2009 13:53:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ReportHistory](
	[ReportID] [int] IDENTITY(1,1) NOT NULL,
	[ReportDate] [smalldatetime] NOT NULL,
	[ReportType] [nvarchar](50) NOT NULL,
	[ReportFileName] [nvarchar](200) NULL,
	[ReportContent] [ntext] NULL,
 CONSTRAINT [PK_ReportHistory] PRIMARY KEY CLUSTERED 
(
	[ReportID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

USE [PreContract]
GO

/****** Object:  Table [dbo].[ReportSearchDefaultMonth]    Script Date: 07/03/2009 13:53:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ReportSearchDefaultMonth](
	[ReportSearchDefaultMonthID] [int] IDENTITY(1,1) NOT NULL,
	[DefaultMonth] [int] NOT NULL,
	[UpdateDate] [smalldatetime] NOT NULL,
 CONSTRAINT [PK_ReportSearchDefaultMonth] PRIMARY KEY CLUSTERED 
(
	[ReportSearchDefaultMonthID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [PreContract]
GO

/****** Object:  Table [dbo].[Role]    Script Date: 07/03/2009 13:53:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Role](
	[RoleID] [int] IDENTITY(1,1) NOT NULL,
	[RoleName] [nvarchar](20) NOT NULL,
	[Description] [nvarchar](100) NULL,
 CONSTRAINT [PK__Role__09946309] PRIMARY KEY CLUSTERED 
(
	[RoleID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [PreContract]
GO

/****** Object:  Table [dbo].[SysRefDate]    Script Date: 07/03/2009 13:53:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[SysRefDate](
	[DateID] [int] IDENTITY(1,1) NOT NULL,
	[RefYear] [smallint] NOT NULL,
	[RefQuater] [tinyint] NOT NULL,
	[RefMonth] [tinyint] NOT NULL,
	[RefDate] [smalldatetime] NOT NULL,
 CONSTRAINT [PK_SysRefDate] PRIMARY KEY CLUSTERED 
(
	[DateID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [PreContract]
GO

/****** Object:  Table [dbo].[TempContractList]    Script Date: 07/03/2009 13:53:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TempContractList](
	[TempID] [int] IDENTITY(1,1) NOT NULL,
	[GUID] [nvarchar](50) NOT NULL,
	[ContractNo] [nvarchar](20) NOT NULL,
	[UpdateDate] [smalldatetime] NOT NULL,
 CONSTRAINT [PK_TempContractList] PRIMARY KEY CLUSTERED 
(
	[TempID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [PreContract]
GO

/****** Object:  Table [dbo].[UserRole]    Script Date: 07/03/2009 13:53:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[UserRole](
	[UserRoleID] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeID] [int] NOT NULL,
	[RoleID] [int] NOT NULL,
 CONSTRAINT [PK_UserRole] PRIMARY KEY CLUSTERED 
(
	[UserRoleID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[AdvanceSearch]  WITH CHECK ADD  CONSTRAINT [FK__AdvanceSe__Emplo__0B7CAB7B] FOREIGN KEY([EmployeeID])
REFERENCES [dbo].[Employee] ([EmployeeID])
GO

ALTER TABLE [dbo].[AdvanceSearch] CHECK CONSTRAINT [FK__AdvanceSe__Emplo__0B7CAB7B]
GO

ALTER TABLE [dbo].[BD]  WITH CHECK ADD  CONSTRAINT [FK__BD__BGID__0C70CFB4] FOREIGN KEY([BGID])
REFERENCES [dbo].[BG] ([BGID])
GO

ALTER TABLE [dbo].[BD] CHECK CONSTRAINT [FK__BD__BGID__0C70CFB4]
GO

ALTER TABLE [dbo].[BD] ADD  CONSTRAINT [DF_BD_Active]  DEFAULT ((1)) FOR [Active]
GO

ALTER TABLE [dbo].[BDAssignment]  WITH CHECK ADD  CONSTRAINT [FK__BDAssignm__Emplo__0E591826] FOREIGN KEY([EmployeeID])
REFERENCES [dbo].[Employee] ([EmployeeID])
GO

ALTER TABLE [dbo].[BDAssignment] CHECK CONSTRAINT [FK__BDAssignm__Emplo__0E591826]
GO

ALTER TABLE [dbo].[BDAssignment]  WITH CHECK ADD  CONSTRAINT [FK__BDAssignme__BDID__0D64F3ED] FOREIGN KEY([BDID])
REFERENCES [dbo].[BD] ([BDID])
GO

ALTER TABLE [dbo].[BDAssignment] CHECK CONSTRAINT [FK__BDAssignme__BDID__0D64F3ED]
GO

ALTER TABLE [dbo].[BG] ADD  CONSTRAINT [DF_BG_Active]  DEFAULT ((1)) FOR [Active]
GO

ALTER TABLE [dbo].[BG] ADD  CONSTRAINT [DF_BG_CreateDate]  DEFAULT (getdate()) FOR [CreateDate]
GO

ALTER TABLE [dbo].[BG] ADD  CONSTRAINT [DF_BG_UpdateDate]  DEFAULT (getdate()) FOR [UpdateDate]
GO

ALTER TABLE [dbo].[BGAssignment]  WITH CHECK ADD  CONSTRAINT [FK__BGAssignm__Emplo__10416098] FOREIGN KEY([EmployeeID])
REFERENCES [dbo].[Employee] ([EmployeeID])
GO

ALTER TABLE [dbo].[BGAssignment] CHECK CONSTRAINT [FK__BGAssignm__Emplo__10416098]
GO

ALTER TABLE [dbo].[BGAssignment]  WITH CHECK ADD  CONSTRAINT [FK__BGAssignme__BGID__0F4D3C5F] FOREIGN KEY([BGID])
REFERENCES [dbo].[BG] ([BGID])
GO

ALTER TABLE [dbo].[BGAssignment] CHECK CONSTRAINT [FK__BGAssignme__BGID__0F4D3C5F]
GO

ALTER TABLE [dbo].[CAT] ADD  CONSTRAINT [DF_CAT_Active]  DEFAULT ((1)) FOR [Active]
GO

ALTER TABLE [dbo].[CATAssignment]  WITH CHECK ADD  CONSTRAINT [FK_CATAssignment_CAT] FOREIGN KEY([CATID])
REFERENCES [dbo].[CAT] ([CATID])
GO

ALTER TABLE [dbo].[CATAssignment] CHECK CONSTRAINT [FK_CATAssignment_CAT]
GO

ALTER TABLE [dbo].[CATAssignment]  WITH CHECK ADD  CONSTRAINT [FK_CATAssignment_Employee] FOREIGN KEY([EmployeeID])
REFERENCES [dbo].[Employee] ([EmployeeID])
GO

ALTER TABLE [dbo].[CATAssignment] CHECK CONSTRAINT [FK_CATAssignment_Employee]
GO

ALTER TABLE [dbo].[ContractDelivery]  WITH CHECK ADD  CONSTRAINT [FK__ContractD__PreCo__131DCD43] FOREIGN KEY([PreContractID])
REFERENCES [dbo].[PreContractInfo] ([PreContractID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[ContractDelivery] CHECK CONSTRAINT [FK__ContractD__PreCo__131DCD43]
GO

ALTER TABLE [dbo].[ContractTransfer]  WITH CHECK ADD  CONSTRAINT [FK__ContractT__PreCo__1411F17C] FOREIGN KEY([PreContractID])
REFERENCES [dbo].[PreContractInfo] ([PreContractID])
GO

ALTER TABLE [dbo].[ContractTransfer] CHECK CONSTRAINT [FK__ContractT__PreCo__1411F17C]
GO

ALTER TABLE [dbo].[DicItem]  WITH CHECK ADD  CONSTRAINT [FK__DicItem__DicType__150615B5] FOREIGN KEY([DicTypeID])
REFERENCES [dbo].[DicType] ([DicTypeID])
GO

ALTER TABLE [dbo].[DicItem] CHECK CONSTRAINT [FK__DicItem__DicType__150615B5]
GO

ALTER TABLE [dbo].[DicItem] ADD  CONSTRAINT [DF_DicItem_IsEnabled]  DEFAULT ((1)) FOR [IsEnabled]
GO

ALTER TABLE [dbo].[DicType] ADD  CONSTRAINT [DF_DicType_IsSystem]  DEFAULT ((1)) FOR [IsSystem]
GO

ALTER TABLE [dbo].[DicType] ADD  CONSTRAINT [DF_DicType_IsEnabled]  DEFAULT ((1)) FOR [IsEnabled]
GO

ALTER TABLE [dbo].[Email] ADD  CONSTRAINT [DF__Email__IsSend__7C8480AE]  DEFAULT ((0)) FOR [IsSend]
GO

ALTER TABLE [dbo].[Employee] ADD  CONSTRAINT [DF_Employee_Active]  DEFAULT ((0)) FOR [Active]
GO

ALTER TABLE [dbo].[Employee] ADD  CONSTRAINT [DF_Employee_CommentAllowed]  DEFAULT ((0)) FOR [CommentAllowed]
GO

ALTER TABLE [dbo].[Employee] ADD  CONSTRAINT [DF_Employee_ASBOrLucent]  DEFAULT ((0)) FOR [ASBOrLucent]
GO

ALTER TABLE [dbo].[Employee] ADD  CONSTRAINT [DF_Employee_DownloadAllowed]  DEFAULT ((0)) FOR [DownloadAllowed]
GO

ALTER TABLE [dbo].[ExchangeRate] ADD  CONSTRAINT [DF_ExchangeRate_UpdateDate]  DEFAULT (getdate()) FOR [UpdateDate]
GO

ALTER TABLE [dbo].[HelpDoc] ADD  CONSTRAINT [DF_HelpDoc_CreatedDate]  DEFAULT (getdate()) FOR [DateCreated]
GO

ALTER TABLE [dbo].[MenuRoleMapping]  WITH CHECK ADD  CONSTRAINT [FK_MenuRoleMapping_MenuConfig] FOREIGN KEY([MenuID])
REFERENCES [dbo].[MenuConfig] ([MenuID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[MenuRoleMapping] CHECK CONSTRAINT [FK_MenuRoleMapping_MenuConfig]
GO

ALTER TABLE [dbo].[MenuRoleMapping]  WITH CHECK ADD  CONSTRAINT [FK_MenuRoleMapping_Role] FOREIGN KEY([RoleID])
REFERENCES [dbo].[Role] ([RoleID])
GO

ALTER TABLE [dbo].[MenuRoleMapping] CHECK CONSTRAINT [FK_MenuRoleMapping_Role]
GO

ALTER TABLE [dbo].[PreContractInfo]  WITH CHECK ADD  CONSTRAINT [FK_PreContractInfo_BD] FOREIGN KEY([BDID])
REFERENCES [dbo].[BD] ([BDID])
GO

ALTER TABLE [dbo].[PreContractInfo] CHECK CONSTRAINT [FK_PreContractInfo_BD]
GO

ALTER TABLE [dbo].[PreContractInfo]  WITH CHECK ADD  CONSTRAINT [FK_PreContractInfo_CAT] FOREIGN KEY([CATID])
REFERENCES [dbo].[CAT] ([CATID])
GO

ALTER TABLE [dbo].[PreContractInfo] CHECK CONSTRAINT [FK_PreContractInfo_CAT]
GO

ALTER TABLE [dbo].[PreContractInfo]  WITH CHECK ADD  CONSTRAINT [FK_PreContractInfo_DicItem] FOREIGN KEY([StatusID])
REFERENCES [dbo].[DicItem] ([DicItemID])
GO

ALTER TABLE [dbo].[PreContractInfo] CHECK CONSTRAINT [FK_PreContractInfo_DicItem]
GO

ALTER TABLE [dbo].[PreContractInfo]  WITH CHECK ADD  CONSTRAINT [FK_PreContractInfo_DicItem1] FOREIGN KEY([TypeID])
REFERENCES [dbo].[DicItem] ([DicItemID])
GO

ALTER TABLE [dbo].[PreContractInfo] CHECK CONSTRAINT [FK_PreContractInfo_DicItem1]
GO

ALTER TABLE [dbo].[PreContractInfo]  WITH CHECK ADD  CONSTRAINT [FK_PreContractInfo_Product] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Product] ([ProductID])
GO

ALTER TABLE [dbo].[PreContractInfo] CHECK CONSTRAINT [FK_PreContractInfo_Product]
GO

ALTER TABLE [dbo].[PreContractInfo]  WITH CHECK ADD  CONSTRAINT [FK_PreContractInfo_Province] FOREIGN KEY([ProvinceID])
REFERENCES [dbo].[Province] ([ProvinceID])
GO

ALTER TABLE [dbo].[PreContractInfo] CHECK CONSTRAINT [FK_PreContractInfo_Province]
GO

ALTER TABLE [dbo].[PreContractInfo] ADD  CONSTRAINT [DF_PreContractInfo_Delivered]  DEFAULT ((0)) FOR [Delivered]
GO

ALTER TABLE [dbo].[PreContractInfo] ADD  CONSTRAINT [DF_PreContractInfo_IsRisk]  DEFAULT ((0)) FOR [IsRisk]
GO

ALTER TABLE [dbo].[PreContractInfoComment]  WITH CHECK ADD  CONSTRAINT [FK_PreContractInfoComment_Employee] FOREIGN KEY([Creator])
REFERENCES [dbo].[Employee] ([EmployeeID])
GO

ALTER TABLE [dbo].[PreContractInfoComment] CHECK CONSTRAINT [FK_PreContractInfoComment_Employee]
GO

ALTER TABLE [dbo].[PreContractInfoComment]  WITH CHECK ADD  CONSTRAINT [FK_PreContractInfoComment_Employee1] FOREIGN KEY([ModifiedBy])
REFERENCES [dbo].[Employee] ([EmployeeID])
GO

ALTER TABLE [dbo].[PreContractInfoComment] CHECK CONSTRAINT [FK_PreContractInfoComment_Employee1]
GO

ALTER TABLE [dbo].[PreContractInfoComment]  WITH CHECK ADD  CONSTRAINT [FK_PreContractInfoComment_PreContractInfo] FOREIGN KEY([PreContractID])
REFERENCES [dbo].[PreContractInfo] ([PreContractID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[PreContractInfoComment] CHECK CONSTRAINT [FK_PreContractInfoComment_PreContractInfo]
GO

ALTER TABLE [dbo].[PreContractInfoComment] ADD  CONSTRAINT [DF_PreContractInfoComment_DateCreated]  DEFAULT (getdate()) FOR [DateCreated]
GO

ALTER TABLE [dbo].[PreContractInfoComment] ADD  CONSTRAINT [DF_PreContractInfoComment_DateModified]  DEFAULT (getdate()) FOR [DateModified]
GO

ALTER TABLE [dbo].[PreContractInfoComment] ADD  CONSTRAINT [DF_PreContractInfoComment_IsRisk]  DEFAULT ((0)) FOR [IsRisk]
GO

ALTER TABLE [dbo].[Product]  WITH CHECK ADD  CONSTRAINT [FK_Product_DicItem] FOREIGN KEY([UnitID])
REFERENCES [dbo].[DicItem] ([DicItemID])
GO

ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [FK_Product_DicItem]
GO

ALTER TABLE [dbo].[Province] ADD  CONSTRAINT [DF_Province_IsEnabled]  DEFAULT ((0)) FOR [Active]
GO

ALTER TABLE [dbo].[ProvinceAssignment]  WITH CHECK ADD  CONSTRAINT [FK__ProvinceA__Emplo__1F83A428] FOREIGN KEY([EmployeeID])
REFERENCES [dbo].[Employee] ([EmployeeID])
GO

ALTER TABLE [dbo].[ProvinceAssignment] CHECK CONSTRAINT [FK__ProvinceA__Emplo__1F83A428]
GO

ALTER TABLE [dbo].[ProvinceAssignment]  WITH CHECK ADD  CONSTRAINT [FK__ProvinceA__Provi__2077C861] FOREIGN KEY([ProvinceID])
REFERENCES [dbo].[Province] ([ProvinceID])
GO

ALTER TABLE [dbo].[ProvinceAssignment] CHECK CONSTRAINT [FK__ProvinceA__Provi__2077C861]
GO

ALTER TABLE [dbo].[TempContractList] ADD  CONSTRAINT [DF_TempContractList_UpdateDate]  DEFAULT (getdate()) FOR [UpdateDate]
GO

ALTER TABLE [dbo].[UserRole]  WITH CHECK ADD  CONSTRAINT [FK__UserRole__Employ__216BEC9A] FOREIGN KEY([EmployeeID])
REFERENCES [dbo].[Employee] ([EmployeeID])
GO

ALTER TABLE [dbo].[UserRole] CHECK CONSTRAINT [FK__UserRole__Employ__216BEC9A]
GO

ALTER TABLE [dbo].[UserRole]  WITH CHECK ADD  CONSTRAINT [FK__UserRole__RoleID__226010D3] FOREIGN KEY([RoleID])
REFERENCES [dbo].[Role] ([RoleID])
GO

ALTER TABLE [dbo].[UserRole] CHECK CONSTRAINT [FK__UserRole__RoleID__226010D3]
GO


/****** Object:  UserDefinedFunction [dbo].[GetSortNumber]    Script Date: 07/03/2009 13:54:44 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetSortNumber]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[GetSortNumber]
GO

/****** Object:  UserDefinedFunction [dbo].[GetSortNumber]    Script Date: 07/03/2009 13:54:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Joan.Lu>
-- Create date: <2008-09-18>
-- Description:	<get max sortnumber>
-- =============================================
CREATE FUNCTION [dbo].[GetSortNumber] 
(
	@TblName NVARCHAR(20)
)
RETURNS  INT
AS
BEGIN
	-- Declare the return variable here
	DECLARE @SortNumber INT
	IF @TblName='Province'
		SELECT @SortNumber=MAX(SortNumber) FROM dbo.Province 
	ELSE IF @TblName='CAT'
		SELECT @SortNumber=MAX(SortNumber) FROM dbo.CAT 
	ELSE IF @TblName='BD'
		SELECT @SortNumber=MAX(SortNumber) FROM dbo.BD 
	--ELSE IF @TblName='CAT'
	--	SELECT @SortNumber=MAX(SortNumber) FROM dbo.CAT 

	-- Return the result of the function
	RETURN @SortNumber
END

GO


/****** Object:  View [dbo].[vw_DimProvinceCAT]    Script Date: 07/03/2009 13:55:07 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_DimProvinceCAT]'))
DROP VIEW [dbo].[vw_DimProvinceCAT]
GO

/****** Object:  View [dbo].[vw_LastOperation]    Script Date: 07/03/2009 13:55:07 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_LastOperation]'))
DROP VIEW [dbo].[vw_LastOperation]
GO

/****** Object:  View [dbo].[vw_PreContract]    Script Date: 07/03/2009 13:55:07 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_PreContract]'))
DROP VIEW [dbo].[vw_PreContract]
GO

/****** Object:  View [dbo].[vw_PreContractFact]    Script Date: 07/03/2009 13:55:07 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_PreContractFact]'))
DROP VIEW [dbo].[vw_PreContractFact]
GO

/****** Object:  View [dbo].[vw_PreContractFactForDelayReport]    Script Date: 07/03/2009 13:55:07 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_PreContractFactForDelayReport]'))
DROP VIEW [dbo].[vw_PreContractFactForDelayReport]
GO

/****** Object:  View [dbo].[vw_PreContractInfo]    Script Date: 07/03/2009 13:55:07 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_PreContractInfo]'))
DROP VIEW [dbo].[vw_PreContractInfo]
GO

/****** Object:  View [dbo].[vw_PreContractInfo_Grus]    Script Date: 07/03/2009 13:55:07 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_PreContractInfo_Grus]'))
DROP VIEW [dbo].[vw_PreContractInfo_Grus]
GO

/****** Object:  View [dbo].[vw_PreContractInfoComment]    Script Date: 07/03/2009 13:55:07 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_PreContractInfoComment]'))
DROP VIEW [dbo].[vw_PreContractInfoComment]
GO

/****** Object:  View [dbo].[vw_PreContractInfoComment_Newest]    Script Date: 07/03/2009 13:55:07 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_PreContractInfoComment_Newest]'))
DROP VIEW [dbo].[vw_PreContractInfoComment_Newest]
GO

/****** Object:  View [dbo].[vw_DimProvinceCAT]    Script Date: 07/03/2009 13:55:07 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE VIEW [dbo].[vw_DimProvinceCAT]
AS

	SELECT a.*,b.ProvinceCName,c.CATCode,

	CASE WHEN b.SortNumber IS NULL THEN dbo.GetSortNumber('Province')+1
		ELSE b.SortNumber 
	END AS ProvSort,
	CASE WHEN c.SortNumber IS NULL THEN dbo.GetSortNumber('CAT')+1
		ELSE c.SortNumber 
	END AS CATSort

	FROM 
		(
			SELECT a.ProvinceID,b.CATID
				FROM dbo.Province a,dbo.CAT b
				GROUP BY a.ProvinceID,b.CATID
				WITH ROLLUP
		) a 
		LEFT OUTER JOIN dbo.Province b ON a.ProvinceID=b.ProvinceID
		LEFT OUTER  JOIN dbo.CAT c ON a.CATID=c.CATID

	
GO

/****** Object:  View [dbo].[vw_LastOperation]    Script Date: 07/03/2009 13:55:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vw_LastOperation]
AS
SELECT a.* FROM OperationLog a
INNER JOIN 
(SELECT EmployeeName,OperationType,MAX(OperationDate) OperationDate 
FROM dbo.OperationLog GROUP BY EmployeeName,OperationType) b

ON a.EmployeeName=b.EmployeeName
	AND a.OperationType=b.OperationType
	AND a.OperationDate=b.OperationDate
GO

/****** Object:  View [dbo].[vw_PreContract]    Script Date: 07/03/2009 13:55:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Joan.Lu>
-- Create date: <2008-09-18>
-- Description:	<get max sortnumber>
-- =============================================
CREATE VIEW [dbo].[vw_PreContract]
AS
SELECT a.ProvinceID,a.CATID,a.BDID,b.BGID,
		YEAR(a.TradeMonth) TradYear,DATEPART(q,a.TradeMonth) TradeQuater,
		MONTH(a.TradeMonth) TradeMonth,TradeMonth AS TradeDate,
		CASE WHEN a.StatusID = 12 THEN a.SalePrice
			ELSE 0
		END AS TransformedRMB,

		CASE WHEN a.Delivered=1 AND (a.StatusID = 10 OR a.StatusID = 11)  THEN a.OrionPrice 
			ELSE 0 
		END AS DeliveredRMB,

		CASE WHEN (a.StatusID = 10 OR a.StatusID = 11) THEN a.OrionPrice
			ELSE 0
		END AS ToBeTransformedRMB,
		a.ASBorLucent

	FROM dbo.PreContractInfo a INNER JOIN dbo.BD b
		ON a.BDID=b.BDID

GO

/****** Object:  View [dbo].[vw_PreContractFact]    Script Date: 07/03/2009 13:55:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Joan.Lu>
-- Create date: <2008-09-19>
-- Description:	<view for report>
-- =============================================
CREATE VIEW [dbo].[vw_PreContractFact]
AS
SELECT a.ProvinceID,a.CATID,a.BDID,b.BGID,
		YEAR(a.TradeMonth) TradeYear,DATEPART(q,a.TradeMonth) TradeQuater,
		MONTH(a.TradeMonth) TradeMonth,TradeMonth AS TradeDate,
		CASE WHEN a.StatusID = 12 THEN a.SalePrice
			ELSE 0
		END AS TransformedRMB,
		CASE WHEN a.StatusID = 12 THEN 1
			ELSE 0
		END AS TransformedCount,

		CASE WHEN a.Delivered=1 AND (a.StatusID = 10 OR a.StatusID = 11)  THEN a.OrionPrice 
			ELSE 0 
		END AS DeliveredRMB,
		CASE WHEN a.Delivered=1 AND (a.StatusID = 10 OR a.StatusID = 11)  THEN 1 
			ELSE 0 
		END AS DeliveredCount,

		CASE WHEN (a.StatusID = 10 OR a.StatusID = 11) THEN a.OrionPrice
			ELSE 0
		END AS ToBeTransformedRMB,
		CASE WHEN (a.StatusID = 10 OR a.StatusID = 11) THEN 1
			ELSE 0
		END AS ToBeTransformedCount,
		--CASE WHEN (a.StatusID = 10 OR a.StatusID = 11) THEN a.CEWPromised
		--	ELSE 0
		--END AS CEWPromised,
		a.ASBorLucent,a.StatusID,a.IsRisk--,a.BlackDate

	FROM dbo.PreContractInfo a 
		INNER JOIN dbo.BD b ON a.BDID=b.BDID
		INNER JOIN dbo.CAT c ON a.CATID=c.CATID
		INNER JOIN dbo.Province d ON a.ProvinceID=d.ProvinceID

	WHERE YEAR(a.TradeMonth)>=YEAR(GETDATE())-1
		AND b.Active=1 AND c.Active=1 AND d.Active=1

GO

/****** Object:  View [dbo].[vw_PreContractFactForDelayReport]    Script Date: 07/03/2009 13:55:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Joan.Lu>
-- Create date: <2008-09-27>
-- Description:	<get max sortnumber>
-- =============================================
CREATE VIEW [dbo].[vw_PreContractFactForDelayReport]
AS
SELECT a.ProvinceID,a.CATID,a.BDID,--b.BGID,
		--YEAR(a.TradeMonth) TradeYear,DATEPART(q,a.TradeMonth) TradeQuater,
		--MONTH(a.TradeMonth) TradeMonth,TradeMonth AS TradeDate,
		--CASE WHEN a.StatusID = 12 THEN a.SalePrice
		--	ELSE 0
		--END AS TransformedRMB,

		CASE WHEN a.Delivered=1 AND (a.StatusID = 10 OR a.StatusID = 11)  THEN a.OrionPrice 
			ELSE 0 
		END AS DeliveredRMB,
		CASE WHEN a.Delivered=1 AND (a.StatusID = 10 OR a.StatusID = 11)  THEN 1
			ELSE 0 
		END AS DeliveredCount,
		CASE WHEN (a.StatusID = 10 OR a.StatusID = 11) THEN a.OrionPrice
			ELSE 0
		END AS ToBeTransformedRMB,
		CASE WHEN (a.StatusID = 10 OR a.StatusID = 11) THEN	1
			ELSE 0
		END AS ToBeTransformedCount,
		--CASE WHEN (a.StatusID = 10 OR a.StatusID = 11) THEN a.CEWPromised
		--	ELSE 0
		--END AS CEWPromised,
		a.ASBorLucent,a.StatusID,a.PromisedDate
	FROM dbo.PreContractInfo a 
		INNER JOIN dbo.BD b ON a.BDID=b.BDID
		INNER JOIN dbo.CAT c ON a.CATID=c.CATID
		INNER JOIN dbo.Province d ON a.ProvinceID=d.ProvinceID
	WHERE a.PromisedDate< DATEADD(mm,1,GETDATE()) AND (a.StatusID =10 OR a.StatusID =11)
		AND b.Active=1 AND c.Active=1 AND d.Active=1

GO

/****** Object:  View [dbo].[vw_PreContractInfo]    Script Date: 07/03/2009 13:55:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vw_PreContractInfo]
AS
SELECT     TOP (100) PERCENT dbo.PreContractInfo.PreContractID, dbo.PreContractInfo.PreContractNo, dbo.PreContractInfo.StatusID, dbo.PreContractInfo.TypeID, 
                      dbo.PreContractInfo.ProductID, dbo.PreContractInfo.PromisedDate, dbo.PreContractInfo.CEWPromised, dbo.PreContractInfo.OrionPrice, 
                      dbo.PreContractInfo.SalePrice, dbo.PreContractInfo.SignDue, dbo.PreContractInfo.SignActual, dbo.PreContractInfo.ContractName, 
                      dbo.PreContractInfo.ERPReceived, dbo.PreContractInfo.OrionNo, dbo.PreContractInfo.Delivered, dbo.PreContractInfo.CurrentSituation, 
                      dbo.PreContractInfo.BlackDate, dbo.PreContractInfo.TradeMonth AS TradeMonth_Date, MONTH(dbo.PreContractInfo.TradeMonth) AS TradeMonth, 
                      YEAR(dbo.PreContractInfo.TradeMonth) AS TradeYear, DATEPART(q, dbo.PreContractInfo.TradeMonth) AS TradeQuater, 
                      dbo.PreContractInfo.ModifiedDate, dbo.PreContractInfo.ACRNo, dbo.PreContractInfo.IsRisk, 
                      CASE dbo.PreContractInfo.ASBorLucent WHEN 0 THEN 'ASB' WHEN 1 THEN 'Lucent' END AS ASBorLucentName, dbo.PreContractInfo.Applicant, 
                      dbo.PreContractInfo.ApplicantEmail, dbo.PreContractInfo.Principal, dbo.PreContractInfo.PrincipalEmail, dbo.PreContractInfo.ProvinceID, 
                      dbo.PreContractInfo.CATID, dbo.PreContractInfo.BDID, dbo.PreContractInfo.CustomerName, dbo.PreContractInfo.CustomField01, 
                      dbo.PreContractInfo.CustomField02, dbo.PreContractInfo.CustomField03, dbo.PreContractInfo.CustomField04, dbo.PreContractInfo.DeliveryPercent, 
                      dbo.CAT.CATCode, dbo.BG.BGCode, dbo.BD.BDCode, dbo.Province.ProvinceCode, dbo.DicItem.DicItemValue AS PreContractStatus, 
                      DicItem_1.DicItemValue AS PreContractType, dbo.Product.ProductName, dbo.PreContractInfo.ASBorLucent, dbo.ExchangeRate.Rate, 
                      ROUND(dbo.PreContractInfo.CEWPromised / dbo.ExchangeRate.Rate / 1170000, 4) AS CEWME, 
                      ROUND(dbo.PreContractInfo.OrionPrice / dbo.ExchangeRate.Rate / 1170000, 4) AS OrionME, 
                      ROUND(dbo.PreContractInfo.OrionPrice / dbo.ExchangeRate.Rate / 1170000, 4) 
                      - ROUND(dbo.PreContractInfo.CEWPromised / dbo.ExchangeRate.Rate / 1170000, 4) AS GAP, dbo.vw_PreContractInfoComment_Newest.Comment, 
                      dbo.PreContractInfo.AdvancedContract, dbo.vw_PreContractInfoComment_Newest.DateCreated, dbo.Province.ProvinceCName, 
                      dbo.vw_PreContractInfoComment_Newest.Creator, dbo.vw_PreContractInfoComment_Newest.CommentPromisedDate, 
                      dbo.vw_PreContractInfoComment_Newest.Comment AS Comments, dbo.BG.BGID, 
                      CASE precontractinfo.IsRisk WHEN 0 THEN 'Secure' WHEN 1 THEN 'Risk' END AS IsRiskDesc, dbo.BD.Active AS BDActive, 
                      dbo.Province.Active AS ProvinceActive, dbo.CAT.Active AS CATActive
FROM         dbo.CAT RIGHT OUTER JOIN
                      dbo.PreContractInfo LEFT OUTER JOIN
                      dbo.vw_PreContractInfoComment_Newest ON dbo.PreContractInfo.PreContractID = dbo.vw_PreContractInfoComment_Newest.PreContractID ON 
                      dbo.CAT.CATID = dbo.PreContractInfo.CATID LEFT OUTER JOIN
                      dbo.Province ON dbo.PreContractInfo.ProvinceID = dbo.Province.ProvinceID LEFT OUTER JOIN
                      dbo.DicItem ON dbo.PreContractInfo.StatusID = dbo.DicItem.DicItemID LEFT OUTER JOIN
                      dbo.DicItem AS DicItem_1 ON dbo.PreContractInfo.TypeID = DicItem_1.DicItemID LEFT OUTER JOIN
                      dbo.Product ON dbo.PreContractInfo.ProductID = dbo.Product.ProductID LEFT OUTER JOIN
                      dbo.BG RIGHT OUTER JOIN
                      dbo.BD ON dbo.BG.BGID = dbo.BD.BGID ON dbo.PreContractInfo.BDID = dbo.BD.BDID CROSS JOIN
                      dbo.ExchangeRate
ORDER BY dbo.PreContractInfo.StatusID, dbo.PreContractInfo.PreContractNo

GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[49] 4[12] 3[21] 2) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "CAT"
            Begin Extent = 
               Top = 224
               Left = 584
               Bottom = 339
               Right = 768
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "PreContractInfo"
            Begin Extent = 
               Top = 53
               Left = 37
               Bottom = 168
               Right = 197
            End
            DisplayFlags = 280
            TopColumn = 32
         End
         Begin Table = "vw_PreContractInfoComment_Newest"
            Begin Extent = 
               Top = 56
               Left = 228
               Bottom = 171
               Right = 443
            End
            DisplayFlags = 280
            TopColumn = 5
         End
         Begin Table = "Province"
            Begin Extent = 
               Top = 139
               Left = 542
               Bottom = 254
               Right = 698
            End
            DisplayFlags = 280
            TopColumn = 1
         End
         Begin Table = "DicItem"
            Begin Extent = 
               Top = 73
               Left = 239
               Bottom = 188
               Right = 391
            End
            DisplayFlags = 280
            TopColumn = 1
         End
         Begin Table = "DicItem_1"
            Begin Extent = 
               Top = 117
               Left = 358
               Bottom = 232
               Right = 510
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "Product"
            Begin Extent = 
               Top = 9
               Left = 22
               Bottom = 124
               Right = 174
            End' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_PreContractInfo'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N'
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "BG"
            Begin Extent = 
               Top = 5
               Left = 615
               Bottom = 120
               Right = 767
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "BD"
            Begin Extent = 
               Top = 18
               Left = 427
               Bottom = 133
               Right = 579
            End
            DisplayFlags = 280
            TopColumn = 4
         End
         Begin Table = "ExchangeRate"
            Begin Extent = 
               Top = 172
               Left = 806
               Bottom = 272
               Right = 968
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 59
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 1605
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_PreContractInfo'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_PreContractInfo'
GO

/****** Object:  View [dbo].[vw_PreContractInfo_Grus]    Script Date: 07/03/2009 13:55:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vw_PreContractInfo_Grus]
AS
SELECT     TOP (100) PERCENT dbo.PreContractInfo.PreContractNo, dbo.PreContractInfo.ContractName, dbo.CAT.CATCode, dbo.BG.BGCode, dbo.BD.BDCode, 
                      dbo.Province.ProvinceCode, dbo.Province.ProvinceCName, dbo.DicItem.DicItemValue AS PreContractStatus, 
                      DicItem_1.DicItemValue AS PreContractType
FROM         dbo.CAT RIGHT OUTER JOIN
                      dbo.PreContractInfo ON dbo.CAT.CATID = dbo.PreContractInfo.CATID LEFT OUTER JOIN
                      dbo.Province ON dbo.PreContractInfo.ProvinceID = dbo.Province.ProvinceID LEFT OUTER JOIN
                      dbo.DicItem ON dbo.PreContractInfo.StatusID = dbo.DicItem.DicItemID LEFT OUTER JOIN
                      dbo.DicItem AS DicItem_1 ON dbo.PreContractInfo.TypeID = DicItem_1.DicItemID LEFT OUTER JOIN
                      dbo.BG RIGHT OUTER JOIN
                      dbo.BD ON dbo.BG.BGID = dbo.BD.BGID ON dbo.PreContractInfo.BDID = dbo.BD.BDID
ORDER BY dbo.PreContractInfo.StatusID, dbo.PreContractInfo.PreContractNo

GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[28] 4[34] 2[5] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "CAT"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 121
               Right = 222
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "PreContractInfo"
            Begin Extent = 
               Top = 6
               Left = 260
               Bottom = 121
               Right = 431
            End
            DisplayFlags = 280
            TopColumn = 17
         End
         Begin Table = "Province"
            Begin Extent = 
               Top = 0
               Left = 723
               Bottom = 115
               Right = 879
            End
            DisplayFlags = 280
            TopColumn = 4
         End
         Begin Table = "DicItem"
            Begin Extent = 
               Top = 0
               Left = 468
               Bottom = 115
               Right = 620
            End
            DisplayFlags = 280
            TopColumn = 1
         End
         Begin Table = "DicItem_1"
            Begin Extent = 
               Top = 0
               Left = 574
               Bottom = 126
               Right = 726
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "BG"
            Begin Extent = 
               Top = 76
               Left = 757
               Bottom = 191
               Right = 909
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "BD"
            Begin Extent = 
               Top = 207
               Left = 298
               Bottom = 322
               Right = 450
            End
            DisplayFlags = 280
       ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_PreContractInfo_Grus'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N'     TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 57
         Width = 284
         Width = 2100
         Width = 2340
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1125
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 3045
         Alias = 1845
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_PreContractInfo_Grus'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_PreContractInfo_Grus'
GO

/****** Object:  View [dbo].[vw_PreContractInfoComment]    Script Date: 07/03/2009 13:55:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vw_PreContractInfoComment]
AS
SELECT     dbo.PreContractInfoComment.PreContractInfoCommentID, dbo.PreContractInfoComment.PreContractID, dbo.PreContractInfoComment.Comment, 
                      dbo.PreContractInfoComment.Creator, dbo.PreContractInfoComment.DateCreated, dbo.PreContractInfoComment.ModifiedBy, 
                      dbo.PreContractInfoComment.DateModified, dbo.PreContractInfo.PreContractNo, dbo.Employee.CILAccount, 
                      Employee_1.CILAccount AS ModifiedByName, dbo.BD.BDCode, 
                      CASE precontractinfocomment.isrisk WHEN 0 THEN 'Secure' WHEN 1 THEN 'Risk' END AS IsRiskDesc, dbo.PreContractInfoComment.IsRisk, 
                      dbo.PreContractInfoComment.CommentPromisedDate
FROM         dbo.PreContractInfoComment INNER JOIN
                      dbo.PreContractInfo ON dbo.PreContractInfoComment.PreContractID = dbo.PreContractInfo.PreContractID INNER JOIN
                      dbo.Employee ON dbo.PreContractInfoComment.Creator = dbo.Employee.EmployeeID INNER JOIN
                      dbo.Employee AS Employee_1 ON dbo.PreContractInfoComment.ModifiedBy = Employee_1.EmployeeID INNER JOIN
                      dbo.BD ON dbo.PreContractInfo.BDID = dbo.BD.BDID

GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[41] 4[21] 2[16] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "PreContractInfoComment"
            Begin Extent = 
               Top = 101
               Left = 290
               Bottom = 216
               Right = 505
            End
            DisplayFlags = 280
            TopColumn = 5
         End
         Begin Table = "PreContractInfo"
            Begin Extent = 
               Top = 5
               Left = 306
               Bottom = 143
               Right = 540
            End
            DisplayFlags = 280
            TopColumn = 23
         End
         Begin Table = "Employee"
            Begin Extent = 
               Top = 0
               Left = 668
               Bottom = 115
               Right = 833
            End
            DisplayFlags = 280
            TopColumn = 4
         End
         Begin Table = "Employee_1"
            Begin Extent = 
               Top = 114
               Left = 680
               Bottom = 229
               Right = 845
            End
            DisplayFlags = 280
            TopColumn = 4
         End
         Begin Table = "BD"
            Begin Extent = 
               Top = 79
               Left = 39
               Bottom = 194
               Right = 191
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 14
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_PreContractInfoComment'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N' = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_PreContractInfoComment'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_PreContractInfoComment'
GO

/****** Object:  View [dbo].[vw_PreContractInfoComment_Newest]    Script Date: 07/03/2009 13:55:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vw_PreContractInfoComment_Newest]
AS
SELECT     dbo.PreContractInfoComment.PreContractInfoCommentID, dbo.PreContractInfoComment.PreContractID, dbo.PreContractInfoComment.Comment, 
                      dbo.PreContractInfoComment.DateCreated, dbo.PreContractInfoComment.DateModified, dbo.PreContractInfoComment.CommentPromisedDate, 
                      dbo.Employee.CILAccount AS Creator, Employee_1.CILAccount AS ModifiedBy, 
                      CASE precontractinfocomment.isrisk WHEN 0 THEN 'Secure' WHEN 1 THEN 'Risk' END AS IsRiskDesc
FROM         dbo.PreContractInfoComment INNER JOIN
                          (SELECT     PreContractID, MAX(DateCreated) AS DateCreated
                            FROM          dbo.PreContractInfoComment AS PreContractInfoComment_1
                            GROUP BY PreContractID) AS NewestComment ON dbo.PreContractInfoComment.PreContractID = NewestComment.PreContractID AND 
                      dbo.PreContractInfoComment.DateCreated = NewestComment.DateCreated INNER JOIN
                      dbo.Employee ON dbo.PreContractInfoComment.Creator = dbo.Employee.EmployeeID INNER JOIN
                      dbo.Employee AS Employee_1 ON dbo.PreContractInfoComment.ModifiedBy = Employee_1.EmployeeID

GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "PreContractInfoComment"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 195
               Right = 292
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "NewestComment"
            Begin Extent = 
               Top = 23
               Left = 320
               Bottom = 151
               Right = 567
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "Employee"
            Begin Extent = 
               Top = 0
               Left = 533
               Bottom = 115
               Right = 698
            End
            DisplayFlags = 280
            TopColumn = 4
         End
         Begin Table = "Employee_1"
            Begin Extent = 
               Top = 45
               Left = 616
               Bottom = 160
               Right = 781
            End
            DisplayFlags = 280
            TopColumn = 4
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 9
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_PreContractInfoComment_Newest'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N'Or = 1350
      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_PreContractInfoComment_Newest'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_PreContractInfoComment_Newest'
GO



/****** Object:  StoredProcedure [dbo].[sp_GetBlackListReport]    Script Date: 07/03/2009 13:54:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetBlackListReport]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetBlackListReport]
GO

/****** Object:  StoredProcedure [dbo].[sp_GetDelayReport]    Script Date: 07/03/2009 13:54:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetDelayReport]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetDelayReport]
GO

/****** Object:  StoredProcedure [dbo].[sp_GetReportByBD]    Script Date: 07/03/2009 13:54:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetReportByBD]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetReportByBD]
GO

/****** Object:  StoredProcedure [dbo].[sp_GetReportByBD_Quarterly]    Script Date: 07/03/2009 13:54:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetReportByBD_Quarterly]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetReportByBD_Quarterly]
GO

/****** Object:  StoredProcedure [dbo].[sp_GetReportByBDByCAT]    Script Date: 07/03/2009 13:54:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetReportByBDByCAT]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetReportByBDByCAT]
GO

/****** Object:  StoredProcedure [dbo].[sp_GetReportByBDByCat_Quarterly]    Script Date: 07/03/2009 13:54:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetReportByBDByCat_Quarterly]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetReportByBDByCat_Quarterly]
GO

/****** Object:  StoredProcedure [dbo].[sp_GetReportByCAT]    Script Date: 07/03/2009 13:54:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetReportByCAT]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetReportByCAT]
GO

/****** Object:  StoredProcedure [dbo].[sp_GetReportByCAT_Quarterly]    Script Date: 07/03/2009 13:54:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetReportByCAT_Quarterly]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetReportByCAT_Quarterly]
GO

/****** Object:  StoredProcedure [dbo].[sp_GetReportByCATByBD]    Script Date: 07/03/2009 13:54:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetReportByCATByBD]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetReportByCATByBD]
GO

/****** Object:  StoredProcedure [dbo].[sp_GetReportByCatByBD_Quarterly]    Script Date: 07/03/2009 13:54:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetReportByCatByBD_Quarterly]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetReportByCatByBD_Quarterly]
GO

/****** Object:  StoredProcedure [dbo].[sp_GetReportByProvByBD]    Script Date: 07/03/2009 13:54:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetReportByProvByBD]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetReportByProvByBD]
GO

/****** Object:  StoredProcedure [dbo].[sp_GetReportByProvByBD_Quarterly]    Script Date: 07/03/2009 13:54:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetReportByProvByBD_Quarterly]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetReportByProvByBD_Quarterly]
GO

/****** Object:  StoredProcedure [dbo].[sp_GetReportByProvByCAT]    Script Date: 07/03/2009 13:54:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetReportByProvByCAT]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetReportByProvByCAT]
GO

/****** Object:  StoredProcedure [dbo].[sp_GetReportByProvByCAT_Quarterly]    Script Date: 07/03/2009 13:54:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetReportByProvByCAT_Quarterly]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetReportByProvByCAT_Quarterly]
GO

/****** Object:  StoredProcedure [dbo].[sp_GetReportByProvince]    Script Date: 07/03/2009 13:54:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetReportByProvince]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetReportByProvince]
GO

/****** Object:  StoredProcedure [dbo].[sp_GetReportByProvince_Quarterly]    Script Date: 07/03/2009 13:54:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetReportByProvince_Quarterly]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetReportByProvince_Quarterly]
GO

/****** Object:  StoredProcedure [dbo].[sp_GetRiskProjectReport]    Script Date: 07/03/2009 13:54:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetRiskProjectReport]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetRiskProjectReport]
GO

/****** Object:  StoredProcedure [dbo].[sp_LoadEmployee]    Script Date: 07/03/2009 13:54:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_LoadEmployee]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_LoadEmployee]
GO

/****** Object:  StoredProcedure [dbo].[sp_LoadHistData]    Script Date: 07/03/2009 13:54:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_LoadHistData]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_LoadHistData]
GO

/****** Object:  StoredProcedure [dbo].[sp_SaveReportHistory]    Script Date: 07/03/2009 13:54:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_SaveReportHistory]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_SaveReportHistory]
GO

/****** Object:  StoredProcedure [dbo].[sp_UpdateTradeMonth]    Script Date: 07/03/2009 13:54:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_UpdateTradeMonth]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_UpdateTradeMonth]
GO

/****** Object:  StoredProcedure [dbo].[sp_UpdateTradeMonthAll]    Script Date: 07/03/2009 13:54:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_UpdateTradeMonthAll]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_UpdateTradeMonthAll]
GO


/****** Object:  StoredProcedure [dbo].[sp_GetBlackListReport]    Script Date: 07/03/2009 13:54:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Joan.lu>
-- Create date: <2008-09-24>
-- Description:	<For Get Black List Report>
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetBlackListReport]
@EmployeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @DtToday SMALLDATETIME
	DECLARE @ExRate DECIMAL(8,6)
	DECLARE @ASBOrLucent INT

	--SET @DtToday=GETDATE()
	SET @DtToday=CAST(CAST(YEAR(GETDATE()) AS CHAR(4)) + '-' + CAST(MONTH(GETDATE()) AS CHAR(2)) + '-' + CAST(DAY(GETDATE()) AS CHAR(2)) AS SMALLDATETIME)

	--GET Exchange Rate
	SELECT TOP 1 @ExRate=[Rate] FROM dbo.ExchangeRate ORDER BY UpdateDate DESC
	
	--GET ASBOrLucent Permission
	IF EXISTS (SELECT a.EmployeeID FROM dbo.UserRole a INNER JOIN dbo.Role b ON a.RoleID=b.RoleID 
				WHERE b.RoleName='Admin' AND  a.EmployeeID=@EmployeeID)
		SET @ASBOrLucent=2
	ELSE
		SELECT @ASBOrLucent=ASBOrLucent FROM dbo.Employee WHERE EmployeeID=@EmployeeID

	IF NOT EXISTS (SELECT a.* FROM dbo.UserRole a INNER JOIN dbo.Role b ON a.RoleID=b.RoleID WHERE b.RoleName='Admin' AND  a.EmployeeID=@EmployeeID)
	BEGIN --Non Admin, need to add BD Filter
		--GET ASBOrLucent Permission
		SELECT @ASBOrLucent=ASBOrLucent FROM dbo.Employee WHERE EmployeeID=@EmployeeID
		IF @ASBOrLucent=2
		BEGIN
			SELECT a.ProvinceID,a.CATID, COUNT(*) AS Num,
				SUM(ROUND((a.CEWPromised/1.17/@ExRate/1000000),4)) AS Amount
			FROM dbo.PreContractInfo a 
				INNER JOIN BD b ON a.BDID=b.BDID
				INNER JOIN CAT c ON a.CATID=c.CATID
				INNER JOIN Province d ON a.ProvinceID=d.ProvinceID
				WHERE (a.StatusID=10 OR a.StatusID=11) AND 
					((a.PromisedDate<@DtToday AND a.BlackDate<@DtToday) OR (a.PromisedDate<@DtToday AND a.BlackDate IS NULL))
					AND TypeID<>9 AND b.Active=1 AND c.Active=1 AND d.Active=1 
					AND a.BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=@EmployeeID)
				GROUP BY a.ProvinceID,a.CATID			
		END
		ELSE
		BEGIN
			SET @ASBOrLucent=CAST(@ASBOrLucent AS BIT)
			SELECT a.ProvinceID,a.CATID, COUNT(*) AS Num,
				SUM(ROUND((a.CEWPromised/1.17/@ExRate/1000000),4)) AS Amount
			FROM dbo.PreContractInfo a 
				INNER JOIN BD b ON a.BDID=b.BDID
				INNER JOIN CAT c ON a.CATID=c.CATID
				INNER JOIN Province d ON a.ProvinceID=d.ProvinceID
				
				WHERE (a.StatusID=10 OR a.StatusID=11) AND 
					((a.PromisedDate<@DtToday AND a.BlackDate<@DtToday) OR (a.PromisedDate<@DtToday AND a.BlackDate IS NULL))
					AND TypeID<>9 AND b.Active=1 AND c.Active=1 AND d.Active=1
					AND ASBOrLucent=@ASBOrLucent
					AND a.BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=@EmployeeID)
				GROUP BY a.ProvinceID,a.CATID
		END
	END
	
	ELSE--FOR ADMIN 
	BEGIN 
			SELECT a.ProvinceID,a.CATID, COUNT(*) AS Num,
				SUM(ROUND((a.CEWPromised/1.17/@ExRate/1000000),4)) AS Amount
			FROM dbo.PreContractInfo a 
				INNER JOIN BD b ON a.BDID=b.BDID
				INNER JOIN CAT c ON a.CATID=c.CATID
				INNER JOIN Province d ON a.ProvinceID=d.ProvinceID
				
				WHERE (a.StatusID=10 OR a.StatusID=11) AND 
					((a.PromisedDate<@DtToday AND a.BlackDate<@DtToday) OR (a.PromisedDate<@DtToday AND a.BlackDate IS NULL))
					AND TypeID<>9 AND b.Active=1 AND c.Active=1 AND d.Active=1
			GROUP BY a.ProvinceID,a.CATID
	END

	SET NOCOUNT OFF;
END

GO

/****** Object:  StoredProcedure [dbo].[sp_GetDelayReport]    Script Date: 07/03/2009 13:54:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Joan.lu>
-- Created date: <2008-09-23>
-- Updated date: <2008-11-19>
-- Description:	<For Get Delay Report>
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetDelayReport]
@EmployeeID INT
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @DtToday SMALLDATETIME
	DECLARE @ExRate DECIMAL(8,6)
	--SET @DtToday=GETDATE()
	SET @DtToday=CAST(CAST(YEAR(GETDATE()) AS CHAR(4)) + '-' + CAST(MONTH(GETDATE()) AS CHAR(2)) + '-' + CAST(DAY(GETDATE()) AS CHAR(2)) AS SMALLDATETIME)

	--GET Exchange Rate
	SELECT TOP 1 @ExRate=[Rate] FROM dbo.ExchangeRate ORDER BY UpdateDate DESC

	DECLARE @ASBOrLucent INT
	SELECT @ASBOrLucent=ASBOrLucent FROM dbo.Employee WHERE EmployeeID=@EmployeeID

	
	IF NOT EXISTS (SELECT a.* FROM dbo.UserRole a INNER JOIN dbo.Role b ON a.RoleID=b.RoleID WHERE b.RoleName='Admin' AND  a.EmployeeID=@EmployeeID)
	BEGIN--Non Admin
		IF @ASBOrLucent=2
		BEGIN --Filter By BD
			SELECT a.ProvinceID,a.CATID,'Not Due' AS DelayType,
				COUNT(*) AS Num,
				SUM(ROUND((a.CEWPromised/1.17/@ExRate/1000000),4)) AS Amount
			FROM dbo.PreContractInfo a 
				INNER JOIN BD b ON a.BDID=b.BDID
				INNER JOIN CAT c ON a.CATID=c.CATID
				INNER JOIN Province d ON a.ProvinceID=d.ProvinceID
				WHERE (a.StatusID=10 OR a.StatusID=11) 
					AND PromisedDate>=@DtToday
					AND b.Active=1 AND c.Active=1 AND d.Active=1 
					AND a.BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=@EmployeeID)
				GROUP BY a.ProvinceID,a.CATID
			UNION ALL
			SELECT a.ProvinceID,a.CATID,'0-3 Month' AS DelayType,
				COUNT(*) AS Num,
				SUM(ROUND((a.CEWPromised/1.17/@ExRate/1000000),4)) AS Amount
			FROM dbo.PreContractInfo a 
				INNER JOIN BD b ON a.BDID=b.BDID
				INNER JOIN CAT c ON a.CATID=c.CATID
				INNER JOIN Province d ON a.ProvinceID=d.ProvinceID
				WHERE (a.StatusID=10 OR a.StatusID=11) 
					AND (PromisedDate<@DtToday AND DATEADD(mm,3,PromisedDate)>=@DtToday)
					AND b.Active=1 AND c.Active=1 AND d.Active=1 
					AND a.BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=@EmployeeID)
				GROUP BY a.ProvinceID,a.CATID
			UNION ALL
			SELECT a.ProvinceID,a.CATID,'3-6 Month' AS DelayType,
				COUNT(*) AS Num,
				SUM(ROUND((a.CEWPromised/1.17/@ExRate/1000000),4)) AS Amount
			FROM dbo.PreContractInfo a 
				INNER JOIN BD b ON a.BDID=b.BDID
				INNER JOIN CAT c ON a.CATID=c.CATID
				INNER JOIN Province d ON a.ProvinceID=d.ProvinceID
				WHERE (a.StatusID=10 OR a.StatusID=11) 
					AND (DATEADD(mm,3,PromisedDate)<@DtToday AND DATEADD(mm,6,PromisedDate)>=@DtToday)
					AND b.Active=1 AND c.Active=1 AND d.Active=1 
					AND a.BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=@EmployeeID)
				GROUP BY a.ProvinceID,a.CATID
			UNION ALL
			SELECT a.ProvinceID,a.CATID,'6-12 Month' AS DelayType,
				COUNT(*) AS Num,
				SUM(ROUND((a.CEWPromised/1.17/@ExRate/1000000),4)) AS Amount
			FROM dbo.PreContractInfo a 
				INNER JOIN BD b ON a.BDID=b.BDID
				INNER JOIN CAT c ON a.CATID=c.CATID
				INNER JOIN Province d ON a.ProvinceID=d.ProvinceID
				WHERE (a.StatusID=10 OR a.StatusID=11) 
					AND (DATEADD(mm,6,PromisedDate)<@DtToday AND DATEADD(mm,12,PromisedDate)>=@DtToday)
					AND b.Active=1 AND c.Active=1 AND d.Active=1 
					AND a.BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=@EmployeeID)
				GROUP BY a.ProvinceID,a.CATID
			UNION ALL
			SELECT a.ProvinceID,a.CATID,'12-24 Month' AS DelayType,
				COUNT(*) AS Num,
				SUM(ROUND((a.CEWPromised/1.17/@ExRate/1000000),4)) AS Amount
			FROM dbo.PreContractInfo a 
				INNER JOIN BD b ON a.BDID=b.BDID
				INNER JOIN CAT c ON a.CATID=c.CATID
				INNER JOIN Province d ON a.ProvinceID=d.ProvinceID
				WHERE (a.StatusID=10 OR a.StatusID=11) 
					AND (DATEADD(mm,12,PromisedDate)<@DtToday AND DATEADD(mm,24,PromisedDate)>=@DtToday)
					AND b.Active=1 AND c.Active=1 AND d.Active=1 
					AND a.BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=@EmployeeID)
				GROUP BY a.ProvinceID,a.CATID
			UNION ALL
			SELECT a.ProvinceID,a.CATID,'>24 Month' AS DelayType,
				COUNT(*) AS Num,
				SUM(ROUND((a.CEWPromised/1.17/@ExRate/1000000),4)) AS Amount
			FROM dbo.PreContractInfo a 
				INNER JOIN BD b ON a.BDID=b.BDID
				INNER JOIN CAT c ON a.CATID=c.CATID
				INNER JOIN Province d ON a.ProvinceID=d.ProvinceID
				WHERE (a.StatusID=10 OR a.StatusID=11) 
					AND (DATEADD(mm,24,PromisedDate)<@DtToday)
					AND b.Active=1 AND c.Active=1 AND d.Active=1 
					AND a.BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=@EmployeeID)
				GROUP BY a.ProvinceID,a.CATID					
			UNION ALL
			SELECT a.ProvinceID,a.CATID,'Total' AS DelayType,
				COUNT(*) AS Num,
				SUM(ROUND((a.CEWPromised/1.17/@ExRate/1000000),4)) AS Amount
			FROM dbo.PreContractInfo a 
				INNER JOIN BD b ON a.BDID=b.BDID
				INNER JOIN CAT c ON a.CATID=c.CATID
				INNER JOIN Province d ON a.ProvinceID=d.ProvinceID
				WHERE (a.StatusID=10 OR a.StatusID=11)
					AND (PromisedDate<@DtToday)
					AND b.Active=1 AND c.Active=1 AND d.Active=1 
					AND a.BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=@EmployeeID)
					GROUP BY a.ProvinceID,a.CATID
			UNION ALL
			SELECT a.ProvinceID,a.CATID,'All Total' AS DelayType,
				COUNT(*) AS Num,
				SUM(ROUND((a.CEWPromised/1.17/@ExRate/1000000),4)) AS Amount
			FROM dbo.PreContractInfo a 
				INNER JOIN BD b ON a.BDID=b.BDID
				INNER JOIN CAT c ON a.CATID=c.CATID
				INNER JOIN Province d ON a.ProvinceID=d.ProvinceID
				WHERE (a.StatusID=10 OR a.StatusID=11)
					AND b.Active=1 AND c.Active=1 AND d.Active=1 
					AND a.BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=@EmployeeID)
					GROUP BY a.ProvinceID,a.CATID
		END
		ELSE--Filter by BD and ASBOrLucnt
		BEGIN 
			SELECT a.ProvinceID,a.CATID,'Not Due' AS DelayType,
				COUNT(*) AS Num,
				SUM(ROUND((a.CEWPromised/1.17/@ExRate/1000000),4)) AS Amount
			FROM dbo.PreContractInfo a 
				INNER JOIN BD b ON a.BDID=b.BDID
				INNER JOIN CAT c ON a.CATID=c.CATID
				INNER JOIN Province d ON a.ProvinceID=d.ProvinceID
				WHERE (a.StatusID=10 OR a.StatusID=11) 
					AND PromisedDate>=@DtToday
					AND b.Active=1 AND c.Active=1 AND d.Active=1 
					AND a.BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=@EmployeeID)
					AND a.ASBorLucent=@ASBorLucent
				GROUP BY a.ProvinceID,a.CATID
			UNION ALL
			SELECT a.ProvinceID,a.CATID,'0-3 Month' AS DelayType,
				COUNT(*) AS Num,
				SUM(ROUND((a.CEWPromised/1.17/@ExRate/1000000),4)) AS Amount
			FROM dbo.PreContractInfo a 
				INNER JOIN BD b ON a.BDID=b.BDID
				INNER JOIN CAT c ON a.CATID=c.CATID
				INNER JOIN Province d ON a.ProvinceID=d.ProvinceID
				WHERE (a.StatusID=10 OR a.StatusID=11) 
					AND (PromisedDate<@DtToday AND DATEADD(mm,3,PromisedDate)>=@DtToday)
					AND b.Active=1 AND c.Active=1 AND d.Active=1 
					AND a.BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=@EmployeeID)
					AND a.ASBorLucent=@ASBorLucent
				GROUP BY a.ProvinceID,a.CATID
			UNION ALL
			SELECT a.ProvinceID,a.CATID,'3-6 Month' AS DelayType,
				COUNT(*) AS Num,
				SUM(ROUND((a.CEWPromised/1.17/@ExRate/1000000),4)) AS Amount
			FROM dbo.PreContractInfo a 
				INNER JOIN BD b ON a.BDID=b.BDID
				INNER JOIN CAT c ON a.CATID=c.CATID
				INNER JOIN Province d ON a.ProvinceID=d.ProvinceID
				WHERE (a.StatusID=10 OR a.StatusID=11) 
					AND (DATEADD(mm,3,PromisedDate)<@DtToday AND DATEADD(mm,6,PromisedDate)>=@DtToday)
					AND b.Active=1 AND c.Active=1 AND d.Active=1 
					AND a.BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=@EmployeeID)
					AND a.ASBorLucent=@ASBorLucent
				GROUP BY a.ProvinceID,a.CATID
			UNION ALL
			SELECT a.ProvinceID,a.CATID,'6-12 Month' AS DelayType,
				COUNT(*) AS Num,
				SUM(ROUND((a.CEWPromised/1.17/@ExRate/1000000),4)) AS Amount
			FROM dbo.PreContractInfo a 
				INNER JOIN BD b ON a.BDID=b.BDID
				INNER JOIN CAT c ON a.CATID=c.CATID
				INNER JOIN Province d ON a.ProvinceID=d.ProvinceID
				WHERE (a.StatusID=10 OR a.StatusID=11) 
					AND (DATEADD(mm,6,PromisedDate)<@DtToday AND DATEADD(mm,12,PromisedDate)>=@DtToday)
					AND b.Active=1 AND c.Active=1 AND d.Active=1 
					AND a.BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=@EmployeeID)
					AND a.ASBorLucent=@ASBorLucent
				GROUP BY a.ProvinceID,a.CATID
			UNION ALL
			SELECT a.ProvinceID,a.CATID,'12-24 Month' AS DelayType,
				COUNT(*) AS Num,
				SUM(ROUND((a.CEWPromised/1.17/@ExRate/1000000),4)) AS Amount
			FROM dbo.PreContractInfo a 
				INNER JOIN BD b ON a.BDID=b.BDID
				INNER JOIN CAT c ON a.CATID=c.CATID
				INNER JOIN Province d ON a.ProvinceID=d.ProvinceID
				WHERE (a.StatusID=10 OR a.StatusID=11) 
					AND (DATEADD(mm,12,PromisedDate)<@DtToday AND DATEADD(mm,24,PromisedDate)>=@DtToday)
					AND b.Active=1 AND c.Active=1 AND d.Active=1 
					AND a.BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=@EmployeeID)
					AND a.ASBorLucent=@ASBorLucent
				GROUP BY a.ProvinceID,a.CATID
			UNION ALL
			SELECT a.ProvinceID,a.CATID,'>24 Month' AS DelayType,
				COUNT(*) AS Num,
				SUM(ROUND((a.CEWPromised/1.17/@ExRate/1000000),4)) AS Amount
			FROM dbo.PreContractInfo a 
				INNER JOIN BD b ON a.BDID=b.BDID
				INNER JOIN CAT c ON a.CATID=c.CATID
				INNER JOIN Province d ON a.ProvinceID=d.ProvinceID
				WHERE (a.StatusID=10 OR a.StatusID=11) 
					AND (DATEADD(mm,24,PromisedDate)<@DtToday)
					AND b.Active=1 AND c.Active=1 AND d.Active=1 
					AND a.BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=@EmployeeID)
					AND a.ASBorLucent=@ASBorLucent
				GROUP BY a.ProvinceID,a.CATID					
			UNION ALL
			SELECT a.ProvinceID,a.CATID,'Total' AS DelayType,
				COUNT(*) AS Num,
				SUM(ROUND((a.CEWPromised/1.17/@ExRate/1000000),4)) AS Amount
			FROM dbo.PreContractInfo a 
				INNER JOIN BD b ON a.BDID=b.BDID
				INNER JOIN CAT c ON a.CATID=c.CATID
				INNER JOIN Province d ON a.ProvinceID=d.ProvinceID
				WHERE (a.StatusID=10 OR a.StatusID=11)
					AND (PromisedDate<@DtToday)
					AND b.Active=1 AND c.Active=1 AND d.Active=1 
					AND a.BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=@EmployeeID)
					AND a.ASBorLucent=@ASBorLucent
					GROUP BY a.ProvinceID,a.CATID	
			UNION ALL
			SELECT a.ProvinceID,a.CATID,'All Total' AS DelayType,
				COUNT(*) AS Num,
				SUM(ROUND((a.CEWPromised/1.17/@ExRate/1000000),4)) AS Amount
			FROM dbo.PreContractInfo a 
				INNER JOIN BD b ON a.BDID=b.BDID
				INNER JOIN CAT c ON a.CATID=c.CATID
				INNER JOIN Province d ON a.ProvinceID=d.ProvinceID
				WHERE (a.StatusID=10 OR a.StatusID=11)
					AND (PromisedDate<@DtToday)
					AND b.Active=1 AND c.Active=1 AND d.Active=1 
					AND a.BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=@EmployeeID)
					AND a.ASBorLucent=@ASBorLucent
					GROUP BY a.ProvinceID,a.CATID	
				
		END
	END

	
	ELSE--Admin
	BEGIN
			SELECT a.ProvinceID,a.CATID,'Not Due' AS DelayType,
				COUNT(*) AS Num,
				SUM(ROUND((a.CEWPromised/1.17/@ExRate/1000000),4)) AS Amount
			FROM dbo.PreContractInfo a 
				INNER JOIN BD b ON a.BDID=b.BDID
				INNER JOIN CAT c ON a.CATID=c.CATID
				INNER JOIN Province d ON a.ProvinceID=d.ProvinceID
				WHERE (a.StatusID=10 OR a.StatusID=11) 
					AND PromisedDate>=@DtToday 
					AND b.Active=1 AND c.Active=1 AND d.Active=1 
				GROUP BY a.ProvinceID,a.CATID
			UNION ALL
			SELECT a.ProvinceID,a.CATID,'0-3 Month' AS DelayType,
				COUNT(*) AS Num,
				SUM(ROUND((a.CEWPromised/1.17/@ExRate/1000000),4)) AS Amount
			FROM dbo.PreContractInfo a 
				INNER JOIN BD b ON a.BDID=b.BDID
				INNER JOIN CAT c ON a.CATID=c.CATID
				INNER JOIN Province d ON a.ProvinceID=d.ProvinceID
				WHERE (a.StatusID=10 OR a.StatusID=11) 
					AND (PromisedDate<@DtToday AND DATEADD(mm,3,PromisedDate)>=@DtToday)
					AND b.Active=1 AND c.Active=1 AND d.Active=1 
				GROUP BY a.ProvinceID,a.CATID
			UNION ALL
			SELECT a.ProvinceID,a.CATID,'3-6 Month' AS DelayType,
				COUNT(*) AS Num,
				SUM(ROUND((a.CEWPromised/1.17/@ExRate/1000000),4)) AS Amount
			FROM dbo.PreContractInfo a 
				INNER JOIN BD b ON a.BDID=b.BDID
				INNER JOIN CAT c ON a.CATID=c.CATID
				INNER JOIN Province d ON a.ProvinceID=d.ProvinceID
				WHERE (a.StatusID=10 OR a.StatusID=11) 
					AND (DATEADD(mm,3,PromisedDate)<@DtToday AND DATEADD(mm,6,PromisedDate)>=@DtToday)
					AND b.Active=1 AND c.Active=1 AND d.Active=1 
				GROUP BY a.ProvinceID,a.CATID
			UNION ALL
			SELECT a.ProvinceID,a.CATID,'6-12 Month' AS DelayType,
				COUNT(*) AS Num,
				SUM(ROUND((a.CEWPromised/1.17/@ExRate/1000000),4)) AS Amount
			FROM dbo.PreContractInfo a 
				INNER JOIN BD b ON a.BDID=b.BDID
				INNER JOIN CAT c ON a.CATID=c.CATID
				INNER JOIN Province d ON a.ProvinceID=d.ProvinceID
				WHERE (a.StatusID=10 OR a.StatusID=11) 
					AND (DATEADD(mm,6,PromisedDate)<@DtToday AND DATEADD(mm,12,PromisedDate)>=@DtToday)
					AND b.Active=1 AND c.Active=1 AND d.Active=1 
				GROUP BY a.ProvinceID,a.CATID
			UNION ALL
			SELECT a.ProvinceID,a.CATID,'12-24 Month' AS DelayType,
				COUNT(*) AS Num,
				SUM(ROUND((a.CEWPromised/1.17/@ExRate/1000000),4)) AS Amount
			FROM dbo.PreContractInfo a 
				INNER JOIN BD b ON a.BDID=b.BDID
				INNER JOIN CAT c ON a.CATID=c.CATID
				INNER JOIN Province d ON a.ProvinceID=d.ProvinceID
				WHERE (a.StatusID=10 OR a.StatusID=11) 
					AND (DATEADD(mm,12,PromisedDate)<@DtToday AND DATEADD(mm,24,PromisedDate)>=@DtToday)
					AND b.Active=1 AND c.Active=1 AND d.Active=1 
				GROUP BY a.ProvinceID,a.CATID
			UNION ALL
			SELECT a.ProvinceID,a.CATID,'>24 Month' AS DelayType,
				COUNT(*) AS Num,
				SUM(ROUND((a.CEWPromised/1.17/@ExRate/1000000),4)) AS Amount
			FROM dbo.PreContractInfo a 
				INNER JOIN BD b ON a.BDID=b.BDID
				INNER JOIN CAT c ON a.CATID=c.CATID
				INNER JOIN Province d ON a.ProvinceID=d.ProvinceID
				WHERE (a.StatusID=10 OR a.StatusID=11) 
					AND (DATEADD(mm,24,PromisedDate)<@DtToday)
					AND b.Active=1 AND c.Active=1 AND d.Active=1 
				GROUP BY a.ProvinceID,a.CATID					
			UNION ALL
			SELECT a.ProvinceID,a.CATID,'Total' AS DelayType,
				COUNT(*) AS Num,
				SUM(ROUND((a.CEWPromised/1.17/@ExRate/1000000),4)) AS Amount
			FROM dbo.PreContractInfo a 
				INNER JOIN BD b ON a.BDID=b.BDID
				INNER JOIN CAT c ON a.CATID=c.CATID
				INNER JOIN Province d ON a.ProvinceID=d.ProvinceID
				WHERE (a.StatusID=10 OR a.StatusID=11)
					AND (PromisedDate<@DtToday)
					AND b.Active=1 AND c.Active=1 AND d.Active=1 
				GROUP BY a.ProvinceID,a.CATID
			UNION ALL
			SELECT a.ProvinceID,a.CATID,'All Total' AS DelayType,
				COUNT(*) AS Num,
				SUM(ROUND((a.CEWPromised/1.17/@ExRate/1000000),4)) AS Amount
			FROM dbo.PreContractInfo a 
				INNER JOIN BD b ON a.BDID=b.BDID
				INNER JOIN CAT c ON a.CATID=c.CATID
				INNER JOIN Province d ON a.ProvinceID=d.ProvinceID
				WHERE (a.StatusID=10 OR a.StatusID=11)
					AND b.Active=1 AND c.Active=1 AND d.Active=1 
				GROUP BY a.ProvinceID,a.CATID
	END
	


	SET NOCOUNT OFF;
END

GO

/****** Object:  StoredProcedure [dbo].[sp_GetReportByBD]    Script Date: 07/03/2009 13:54:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Joan.lu>
-- Create date: <2008-09-22>
-- Description:	<For Get ReportValues>
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetReportByBD]
	@NoOfMonth INT, 
	@EmployeeID INT,
	@ASBOrLucent CHAR(1) = '' --0-ASB,1-Lucent,2-ALL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	--CHECK IF Employee has permission
	DECLARE @EntityType TINYINT

	IF @ASBOrLucent=''
	BEGIN
		SET @EntityType=2 --ALL
		SET @ASBOrLucent='2' --ALL
	END
	ELSE 
		SET @EntityType=CAST(@ASBOrLucent AS TINYINT)
			
	--IF NOT EXISTS (SELECT EmployeeID FROM dbo.Employee WHERE EmployeeID=@EmployeeID AND (ASBOrLucent=2 OR ASBOrLucent=@EntityType))
	--	RETURN 100


	DECLARE @DtToday SMALLDATETIME
	DECLARE @DtStart SMALLDATETIME
	DECLARE @DtEnd SMALLDATETIME
	DECLARE @DtCondition NVARCHAR(500)

	DECLARE @ExRate DECIMAL(8,6)
	DECLARE @SQLString NVARCHAR(MAX)

	--GET Trade Month start date and end date
	SET @DtToday=CAST(CAST(YEAR(GETDATE()) AS CHAR(4)) + '-' + CAST(MONTH(GETDATE()) AS CHAR(2)) + '-' + CAST(DAY(GETDATE()) AS CHAR(2)) AS SMALLDATETIME)

	SELECT @DtStart=CAST(CAST([Year] AS CHAR(4)) + '-' + CAST([Month] AS CHAR(2)) + '-01' AS SMALLDATETIME)
		FROM dbo.CloseDate WHERE @DtToday BETWEEN StartDate AND EndDate
	/*
	IF @DtStart IS NULL
	BEGIN
		--SET @StrError='本月关帐日期未设置!Close Date is not set for current month!'
		RETURN 101
	END
	*/

	SET @DtEnd=DATEADD(mm,@NoOfMonth,@DtStart);
	SET @DtEnd=DATEADD(dd,-1,@DtEnd);
	--PRINT @DtEnd


	SET @DtCondition=' BETWEEN ''' + CAST(@DtStart AS VARCHAR(20)) + ''' AND ''' + CAST(@DtEnd AS VARCHAR(20)) + ''' ' 
	DECLARE @YrCondition NVARCHAR(500)
	Declare @CommonCondition nvarchar(500)
	set @CommonCondition = ' 1=1 '
	SET @YrCondition=' TradeYear>=' + CAST(YEAR(@DtStart) AS VARCHAR(4)) + ' AND TradeYear<=' + CAST(YEAR(@DtEnd) AS VARCHAR(4))
	

	--GET Exchange Rate
	SELECT TOP 1 @ExRate=[Rate] FROM dbo.ExchangeRate ORDER BY UpdateDate DESC


	--GET Date list for Report date dimentions
	SET @SQLString='SELECT RefYear, RefQuater,RefMonth FROM dbo.SysRefDate '
		+ ' WHERE RefDate ' + @DtCondition + ' GROUP BY RefYear,RefQuater ,RefMonth WITH ROLLUP'
		+ ' union all select 2046, null, null'

	EXEC sp_executesql @SQLString

	--Add CAT and Prov Filter
	IF NOT EXISTS (SELECT a.* FROM dbo.UserRole a INNER JOIN dbo.Role b ON a.RoleID=b.RoleID WHERE b.RoleName='Admin' AND  a.EmployeeID=@EmployeeID)
	BEGIN
		--SET @DtCondition=@DtCondition + ' AND BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		--SET @YrCondition=@YrCondition + ' AND BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		SET @DtCondition=@DtCondition + ' AND CATID IN (SELECT CATID FROM dbo.CATAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		SET @YrCondition=@YrCondition + ' AND CATID IN (SELECT CATID FROM dbo.CATAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		SET @DtCondition=@DtCondition + ' AND ProvinceID IN (SELECT ProvinceID FROM dbo.ProvinceAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		SET @YrCondition=@YrCondition + ' AND ProvinceID IN (SELECT ProvinceID FROM dbo.ProvinceAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		set @CommonCondition = @CommonCondition  + ' AND CATID IN (SELECT CATID FROM dbo.CATAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		set @CommonCondition = @CommonCondition + ' AND ProvinceID IN (SELECT ProvinceID FROM dbo.ProvinceAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
	END


	IF (@ASBOrLucent<>'2')
	BEGIN
		SET @DtCondition= @DtCondition + ' AND ASBorLucent='+ @ASBOrLucent
		SET @YrCondition= @YrCondition + ' AND ASBorLucent='+ @ASBOrLucent
		set @CommonCondition = @CommonCondition+ ' AND ASBorLucent='+ @ASBOrLucent
	END

	--GET Report Values
	--UNION Aggregated value by Year Quater Month, By Year Quater, By Year
	SET @SQLString='SELECT BGID,BDID,
		TradeYear,TradeQuater,TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
 		SUM(TransformedCount) TransformedCount,
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount
		FROM vw_PreContractFact '
		+ ' WHERE TradeDate ' + @DtCondition + 
		' GROUP BY  BGID,BDID,TradeYear,TradeQuater,TradeMonth

		UNION ALL 

		SELECT BGID,BDID,
		TradeYear,TradeQuater,NULL AS TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount
		FROM vw_PreContractFact '
		+ ' WHERE ' + @YrCondition + 
		' GROUP BY  BGID,BDID,TradeYear,TradeQuater	


		UNION ALL 

		SELECT BGID,BDID,
		TradeYear,NULL AS TradeQuater,NULL AS TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact'
		+ ' WHERE ' + @YrCondition + 
		' GROUP BY  BGID,BDID,TradeYear

		UNION ALL 

		SELECT BGID,BDID,
		2046 AS TradeYear ,NULL AS TradeQuater,NULL AS TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact'
		+ ' WHERE ' + @CommonCondition + 
		' GROUP BY  BGID,BDID'
	--PRINT @SQLString
	EXEC sp_executesql @SQLString

	SET NOCOUNT OFF;
END

GO

/****** Object:  StoredProcedure [dbo].[sp_GetReportByBD_Quarterly]    Script Date: 07/03/2009 13:54:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


Create PROCEDURE [dbo].[sp_GetReportByBD_Quarterly]
	@EmployeeID INT,
	@ASBOrLucent CHAR(1) = '' --0-ASB,1-Lucent,2-ALL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	IF @ASBOrLucent=''
	BEGIN
		SET @ASBOrLucent='2' --ALL
	END

	DECLARE @DtToday SMALLDATETIME
	DECLARE @YrStart int
	DECLARE @QtStart int



	DECLARE @ExRate DECIMAL(8,6)
	DECLARE @SQLString NVARCHAR(MAX)

	--GET Trade Month start date and end date
	SET @DtToday=CAST(CAST(YEAR(GETDATE()) AS CHAR(4)) + '-' + CAST(MONTH(GETDATE()) AS CHAR(2)) + '-' + CAST(DAY(GETDATE()) AS CHAR(2)) AS SMALLDATETIME)

	SELECT @YrStart = [year], @QtStart = quarter
		FROM dbo.CloseDate WHERE @DtToday BETWEEN StartDate AND EndDate

	DECLARE @QtCondition NVARCHAR(500)
	DECLARE @AfterCondition NVARCHAR(500)
	Declare @CommonCondition nvarchar(500)
	set @CommonCondition = ' 1=1 '

	SET @QtCondition=' TradeYear=' + CAST(@YrStart AS VARCHAR(4)) + '  AND TradeQuater=' + CAST(@QtStart AS VARCHAR(1))


	SET @AfterCondition='(  ( TradeYear=' + CAST(@YrStart AS VARCHAR(4)) + ' and TradeQuater> ' + CAST(@QtStart AS VARCHAR(1)) + ') or TradeYear>' +  CAST(@YrStart AS VARCHAR(4)) + ' ) '

	
	--GET Exchange Rate
	SELECT TOP 1 @ExRate=[Rate] FROM dbo.ExchangeRate ORDER BY UpdateDate DESC


	--GET Date list for Report date dimentions
	SET @SQLString='SELECT '+ CAST(@YrStart AS VARCHAR(4)) +' as RefYear, '+ CAST(@QtStart AS VARCHAR(1)) +' as RefQuater, null as RefMonth '
		+ ' union all select 2038, null, null'
		+ ' union all select 2046, null, null'


	EXEC sp_executesql @SQLString

	--Add BD and Prov Filter
	IF NOT EXISTS (SELECT a.* FROM dbo.UserRole a INNER JOIN dbo.Role b ON a.RoleID=b.RoleID WHERE b.RoleName='Admin' AND  a.EmployeeID=@EmployeeID)
	BEGIN
		SET @QtCondition=@QtCondition + ' AND CATID IN (SELECT CATID FROM dbo.CATAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		SET @AfterCondition=@AfterCondition + ' AND CATID IN (SELECT CATID FROM dbo.CATAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
 		set @CommonCondition = @CommonCondition  + ' AND CATID IN (SELECT CATID FROM dbo.CATAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		SET @QtCondition=@QtCondition + ' AND ProvinceID IN (SELECT ProvinceID FROM dbo.ProvinceAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		SET @AfterCondition=@AfterCondition + ' AND ProvinceID IN (SELECT ProvinceID FROM dbo.ProvinceAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		set @CommonCondition = @CommonCondition + ' AND ProvinceID IN (SELECT ProvinceID FROM dbo.ProvinceAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
	END

	IF @ASBOrLucent<>'2'
	BEGIN
		SET @QtCondition= @QtCondition + ' AND ASBorLucent='+ @ASBOrLucent
		SET @AfterCondition= @AfterCondition + ' AND ASBorLucent='+ @ASBOrLucent
		set @CommonCondition = @CommonCondition + ' AND ASBorLucent='+ @ASBOrLucent
	END

	--PRINT @DtCondition
	--GET Report Values
	--UNION Aggregated value by Year Quater Month, By Year Quater, By Year
	SET @SQLString='SELECT BGID,BDID,
		TradeYear,TradeQuater,Null as TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
 		SUM(TransformedCount) TransformedCount,
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount
		FROM vw_PreContractFact '
		+ ' WHERE ' + @QtCondition + 
		' GROUP BY  BGID,BDID,TradeYear,TradeQuater

		UNION ALL 

		SELECT BGID,BDID,
		2038 as TradeYear,NULL AS TradeQuater,NULL AS TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact '
		+ ' WHERE ' + @AfterCondition + 
		' GROUP BY  BGID,BDID

		UNION ALL 

		SELECT BGID,BDID,
		2046 AS TradeYear ,NULL AS TradeQuater,NULL AS TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact'
		+ ' WHERE  ' + @CommonCondition + 
		' GROUP BY  BGID,BDID'


--select 	@SQLString
	EXEC sp_executesql @SQLString

	SET NOCOUNT OFF;
END

GO

/****** Object:  StoredProcedure [dbo].[sp_GetReportByBDByCAT]    Script Date: 07/03/2009 13:54:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Joan.lu>
-- Create date: <2008-11-21>
-- Description:	<For Get ReportValues>
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetReportByBDByCAT]
	@NoOfMonth INT, 
	@EmployeeID INT,
	@ASBOrLucent CHAR(1) = ''--0-ASB,1-Lucent,2-ALL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	--CHECK IF Employee has permission
	DECLARE @EntityType TINYINT

	IF @ASBOrLucent=''
	BEGIN
		SET @EntityType=2 --ALL
		SET @ASBOrLucent='2' --ALL
	END
	ELSE 
		SET @EntityType=CAST(@ASBOrLucent AS TINYINT)
			
	--IF NOT EXISTS (SELECT EmployeeID FROM dbo.Employee WHERE EmployeeID=@EmployeeID AND (ASBOrLucent=0 OR ASBOrLucent=@EntityType))
	--	RETURN 100


	DECLARE @DtToday SMALLDATETIME
	DECLARE @DtStart SMALLDATETIME
	DECLARE @DtEnd SMALLDATETIME
	DECLARE @DtCondition NVARCHAR(500)

	DECLARE @ExRate DECIMAL(8,6)
	DECLARE @SQLString NVARCHAR(MAX)

	--GET Trade Month start date and end date
	SET @DtToday=CAST(CAST(YEAR(GETDATE()) AS CHAR(4)) + '-' + CAST(MONTH(GETDATE()) AS CHAR(2)) + '-' + CAST(DAY(GETDATE()) AS CHAR(2)) AS SMALLDATETIME)

	SELECT @DtStart=CAST(CAST([Year] AS CHAR(4)) + '-' + CAST([Month] AS CHAR(2)) + '-01' AS SMALLDATETIME)
		FROM dbo.CloseDate WHERE @DtToday BETWEEN StartDate AND EndDate
	/*
	IF @DtStart IS NULL
	BEGIN
		--SET @StrError='本月关帐日期未设置!Close Date is not set for current month!'
		RETURN 101
	END
	*/	
	SET @DtEnd=DATEADD(mm,@NoOfMonth,@DtStart);
	SET @DtEnd=DATEADD(dd,-1,@DtEnd);
	--PRINT @DtEnd


	SET @DtCondition=' BETWEEN ''' + CAST(@DtStart AS VARCHAR(20)) + ''' AND ''' + CAST(@DtEnd AS VARCHAR(20)) + ''' ' 
	DECLARE @YrCondition NVARCHAR(500)
	SET @YrCondition=' TradeYear>=' + CAST(YEAR(@DtStart) AS VARCHAR(4)) + ' AND TradeYear<=' + CAST(YEAR(@DtEnd) AS VARCHAR(4))

	--GET Exchange Rate
	SELECT TOP 1 @ExRate=[Rate] FROM dbo.ExchangeRate ORDER BY UpdateDate DESC

	Declare @CommonCondition nvarchar(500)
	set @CommonCondition = ' 1=1 '
	--GET Date list for Report date dimentions
	SET @SQLString='SELECT RefYear, RefQuater,RefMonth FROM dbo.SysRefDate '
		+ ' WHERE RefDate ' + @DtCondition + ' GROUP BY RefYear,RefQuater ,RefMonth WITH ROLLUP'
		+ ' union all select 2046, null, null'

	EXEC sp_executesql @SQLString
	--Add Prov Filter
	IF NOT EXISTS (SELECT a.* FROM dbo.UserRole a INNER JOIN dbo.Role b ON a.RoleID=b.RoleID WHERE b.RoleName='Admin' AND  a.EmployeeID=@EmployeeID)
	BEGIN
		--SET @DtCondition=@DtCondition + ' AND BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		--SET @YrCondition=@YrCondition + ' AND BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		--SET @DtCondition=@DtCondition + ' AND CATID IN (SELECT CATID FROM dbo.CATAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		--SET @YrCondition=@YrCondition + ' AND CATID IN (SELECT CATID FROM dbo.CATAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		SET @DtCondition=@DtCondition + ' AND ProvinceID IN (SELECT ProvinceID FROM dbo.ProvinceAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		SET @YrCondition=@YrCondition + ' AND ProvinceID IN (SELECT ProvinceID FROM dbo.ProvinceAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		set @CommonCondition = @CommonCondition + ' AND ProvinceID IN (SELECT ProvinceID FROM dbo.ProvinceAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
	END
	IF @ASBOrLucent<>'2'
	BEGIN
		SET @DtCondition= @DtCondition + ' AND ASBorLucent='+ @ASBOrLucent
		SET @YrCondition= @YrCondition + ' AND ASBorLucent='+ @ASBOrLucent
		set @CommonCondition = @CommonCondition + ' AND ASBorLucent='+ @ASBOrLucent
	END
	

	--GET Report Values
	--UNION Aggregated value by Year Quater Month, By Year Quater, By Year
	SET @SQLString='SELECT BDID,CATID,
		TradeYear,TradeQuater,TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact '
		+ ' WHERE TradeDate ' + @DtCondition + 
		' GROUP BY  BDID,CATID,TradeYear,TradeQuater,TradeMonth

		UNION ALL 

		SELECT BDID,CATID,
		TradeYear,TradeQuater,NULL AS TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact '
		+ ' WHERE ' + @YrCondition + 
		' GROUP BY  BDID,CATID,TradeYear,TradeQuater	


		UNION ALL 

		SELECT BDID,CATID,
		TradeYear,NULL AS TradeQuater,NULL AS TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact'
		+ ' WHERE ' + @YrCondition + 
		' GROUP BY  BDID,CATID,TradeYear

		UNION ALL 

		SELECT BDID,CATID,
		2046 AS TradeYear,NULL AS TradeQuater,NULL AS TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact'
		+ ' WHERE ' + @CommonCondition + 
		' GROUP BY  BDID,CATID'


	EXEC sp_executesql @SQLString


	SET NOCOUNT OFF;
END

GO

/****** Object:  StoredProcedure [dbo].[sp_GetReportByBDByCat_Quarterly]    Script Date: 07/03/2009 13:54:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


create PROCEDURE [dbo].[sp_GetReportByBDByCat_Quarterly]
	@EmployeeID INT,
	@ASBOrLucent CHAR(1) = '' --0-ASB,1-Lucent,2-ALL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	IF @ASBOrLucent=''
	BEGIN
		SET @ASBOrLucent='2' --ALL
	END

	DECLARE @DtToday SMALLDATETIME
	DECLARE @YrStart int
	DECLARE @QtStart int



	DECLARE @ExRate DECIMAL(8,6)
	DECLARE @SQLString NVARCHAR(MAX)

	--GET Trade Month start date and end date
	SET @DtToday=CAST(CAST(YEAR(GETDATE()) AS CHAR(4)) + '-' + CAST(MONTH(GETDATE()) AS CHAR(2)) + '-' + CAST(DAY(GETDATE()) AS CHAR(2)) AS SMALLDATETIME)

	SELECT @YrStart = [year], @QtStart = quarter
		FROM dbo.CloseDate WHERE @DtToday BETWEEN StartDate AND EndDate

	DECLARE @QtCondition NVARCHAR(500)
	DECLARE @AfterCondition NVARCHAR(500)
	Declare @CommonCondition nvarchar(500)
	set @CommonCondition = ' 1=1 '

	SET @QtCondition=' TradeYear=' + CAST(@YrStart AS VARCHAR(4)) + '  AND TradeQuater=' + CAST(@QtStart AS VARCHAR(1))


	SET @AfterCondition='(  ( TradeYear=' + CAST(@YrStart AS VARCHAR(4)) + ' and TradeQuater> ' + CAST(@QtStart AS VARCHAR(1)) + ') or TradeYear>' +  CAST(@YrStart AS VARCHAR(4)) + ' ) '

	
	--GET Exchange Rate
	SELECT TOP 1 @ExRate=[Rate] FROM dbo.ExchangeRate ORDER BY UpdateDate DESC


	--GET Date list for Report date dimentions
	SET @SQLString='SELECT '+ CAST(@YrStart AS VARCHAR(4)) +' as RefYear, '+ CAST(@QtStart AS VARCHAR(1)) +' as RefQuater, null as RefMonth '
		+ ' union all select 2038, null, null'
		+ ' union all select 2046, null, null'


	EXEC sp_executesql @SQLString

	--Add BD and Prov Filter
	IF NOT EXISTS (SELECT a.* FROM dbo.UserRole a INNER JOIN dbo.Role b ON a.RoleID=b.RoleID WHERE b.RoleName='Admin' AND  a.EmployeeID=@EmployeeID)
	BEGIN
		SET @QtCondition=@QtCondition + ' AND ProvinceID IN (SELECT ProvinceID FROM dbo.ProvinceAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		SET @AfterCondition=@AfterCondition + ' AND ProvinceID IN (SELECT ProvinceID FROM dbo.ProvinceAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		set @CommonCondition = @CommonCondition + ' AND ProvinceID IN (SELECT ProvinceID FROM dbo.ProvinceAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
	END

	IF @ASBOrLucent<>'2'
	BEGIN
		SET @QtCondition= @QtCondition + ' AND ASBorLucent='+ @ASBOrLucent
		SET @AfterCondition= @AfterCondition + ' AND ASBorLucent='+ @ASBOrLucent
		set @CommonCondition = @CommonCondition + ' AND ASBorLucent='+ @ASBOrLucent
	END

	--PRINT @DtCondition
	--GET Report Values
	--UNION Aggregated value by Year Quater Month, By Year Quater, By Year
	SET @SQLString='SELECT BDID,CATID,
		TradeYear,TradeQuater,Null as TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
 		SUM(TransformedCount) TransformedCount,
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount
		FROM vw_PreContractFact '
		+ ' WHERE ' + @QtCondition + 
		' GROUP BY  BDID,CATID,TradeYear,TradeQuater

		UNION ALL 

		SELECT  BDID,CATID,
		2038 as TradeYear,NULL AS TradeQuater,NULL AS TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact '
		+ ' WHERE ' + @AfterCondition + 
		' GROUP BY  BDID,CATID

		UNION ALL 

		SELECT  BDID,CATID,
		2046 AS TradeYear ,NULL AS TradeQuater,NULL AS TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact'
		+ ' WHERE  ' + @CommonCondition + 
		' GROUP BY  BDID,CATID '


--select 	@SQLString
	EXEC sp_executesql @SQLString

	SET NOCOUNT OFF;
END

GO

/****** Object:  StoredProcedure [dbo].[sp_GetReportByCAT]    Script Date: 07/03/2009 13:54:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Joan.lu>
-- Create date: <2008-09-22>
-- Description:	<For Get ReportValues>
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetReportByCAT]
	@NoOfMonth INT, 
	@EmployeeID INT,
	@ASBOrLucent CHAR(1) = '' --0-ASB,1-Lucent,2-ALL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	--CHECK IF Employee has permission
	DECLARE @EntityType TINYINT

	IF @ASBOrLucent=''
	BEGIN
		SET @EntityType=2 --ALL
		SET @ASBOrLucent='2' --ALL
	END
	ELSE
		SET @EntityType=CAST(@ASBOrLucent AS TINYINT)
			
	--IF NOT EXISTS (SELECT EmployeeID FROM dbo.Employee WHERE EmployeeID=@EmployeeID AND (ASBOrLucent=0 OR ASBOrLucent=@EntityType))
	--	RETURN 100


	DECLARE @DtToday SMALLDATETIME
	DECLARE @DtStart SMALLDATETIME
	DECLARE @DtEnd SMALLDATETIME
	DECLARE @DtCondition NVARCHAR(500)



	DECLARE @ExRate DECIMAL(8,6)
	DECLARE @SQLString NVARCHAR(MAX)

	--GET Trade Month start date and end date
	SET @DtToday=CAST(CAST(YEAR(GETDATE()) AS CHAR(4)) + '-' + CAST(MONTH(GETDATE()) AS CHAR(2)) + '-' + CAST(DAY(GETDATE()) AS CHAR(2)) AS SMALLDATETIME)

	SELECT @DtStart=CAST(CAST([Year] AS CHAR(4)) + '-' + CAST([Month] AS CHAR(2)) + '-01' AS SMALLDATETIME)
		FROM dbo.CloseDate WHERE @DtToday BETWEEN StartDate AND EndDate
	/*
	IF @DtStart IS NULL
	BEGIN
		--SET @StrError='本月关帐日期未设置!Close Date is not set for current month!'
		RETURN 101
	END
	*/

	SET @DtEnd=DATEADD(mm,@NoOfMonth,@DtStart);
	SET @DtEnd=DATEADD(dd,-1,@DtEnd);
	--PRINT @DtEnd


	SET @DtCondition=' BETWEEN ''' + CAST(@DtStart AS VARCHAR(20)) + ''' AND ''' + CAST(@DtEnd AS VARCHAR(20)) + ''' ' 
	DECLARE @YrCondition NVARCHAR(500)
	Declare @CommonCondition nvarchar(500)
	set @CommonCondition = ' 1=1 '
	SET @YrCondition=' TradeYear>=' + CAST(YEAR(@DtStart) AS VARCHAR(4)) + ' AND TradeYear<=' + CAST(YEAR(@DtEnd) AS VARCHAR(4))
	
	--GET Exchange Rate
	SELECT TOP 1 @ExRate=[Rate] FROM dbo.ExchangeRate ORDER BY UpdateDate DESC


	--GET Date list for Report date dimentions
	SET @SQLString='SELECT RefYear, RefQuater,RefMonth FROM dbo.SysRefDate '
		+ ' WHERE RefDate ' + @DtCondition + ' GROUP BY RefYear,RefQuater ,RefMonth WITH ROLLUP'
		+ ' union all select 2046, null, null'


	EXEC sp_executesql @SQLString

	--Add BD and Prov Filter
	IF NOT EXISTS (SELECT a.* FROM dbo.UserRole a INNER JOIN dbo.Role b ON a.RoleID=b.RoleID WHERE b.RoleName='Admin' AND  a.EmployeeID=@EmployeeID)
	BEGIN
		SET @DtCondition=@DtCondition + ' AND BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		SET @YrCondition=@YrCondition + ' AND BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		set @CommonCondition = @CommonCondition  + ' AND BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		--SET @DtCondition=@DtCondition + ' AND CATID IN (SELECT CATID FROM dbo.CATAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		--SET @YrCondition=@YrCondition + ' AND CATID IN (SELECT CATID FROM dbo.CATAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		SET @DtCondition=@DtCondition + ' AND ProvinceID IN (SELECT ProvinceID FROM dbo.ProvinceAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		SET @YrCondition=@YrCondition + ' AND ProvinceID IN (SELECT ProvinceID FROM dbo.ProvinceAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		set @CommonCondition = @CommonCondition + ' AND ProvinceID IN (SELECT ProvinceID FROM dbo.ProvinceAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
	END

	IF @ASBOrLucent<>'2'
	BEGIN
		SET @DtCondition= @DtCondition + ' AND ASBorLucent='+ @ASBOrLucent
		SET @YrCondition= @YrCondition + ' AND ASBorLucent='+ @ASBOrLucent
		set @CommonCondition = @CommonCondition + ' AND ASBorLucent='+ @ASBOrLucent
	END

	--PRINT @DtCondition
	--GET Report Values
	--UNION Aggregated value by Year Quater Month, By Year Quater, By Year
	SET @SQLString='SELECT CATID,NULL AS NoUse,
		TradeYear,TradeQuater,TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact '
		+ ' WHERE TradeDate ' + @DtCondition + 
		' GROUP BY  CATID,TradeYear,TradeQuater,TradeMonth

		UNION ALL 

		SELECT CATID,NULL AS NoUse,
		TradeYear,TradeQuater,NULL AS TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact '
		+ ' WHERE ' + @YrCondition + 
		' GROUP BY  CATID,TradeYear,TradeQuater	


		UNION ALL 

		SELECT CATID,NULL AS NoUse,
		TradeYear,NULL AS TradeQuater,NULL AS TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact'
		+ ' WHERE  ' + @YrCondition + 
		' GROUP BY  CATID,TradeYear

		UNION ALL 

		SELECT CATID,NULL AS NoUse,
		2046 AS TradeYear ,NULL AS TradeQuater,NULL AS TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact'
		+ ' WHERE  ' + @CommonCondition + 
		' GROUP BY  CATID'


--select 	@SQLString
	EXEC sp_executesql @SQLString

	SET NOCOUNT OFF;
END

GO

/****** Object:  StoredProcedure [dbo].[sp_GetReportByCAT_Quarterly]    Script Date: 07/03/2009 13:54:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


create PROCEDURE [dbo].[sp_GetReportByCAT_Quarterly]
	@EmployeeID INT,
	@ASBOrLucent CHAR(1) = '' --0-ASB,1-Lucent,2-ALL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	IF @ASBOrLucent=''
	BEGIN
		SET @ASBOrLucent='2' --ALL
	END

	DECLARE @DtToday SMALLDATETIME
	DECLARE @YrStart int
	DECLARE @QtStart int



	DECLARE @ExRate DECIMAL(8,6)
	DECLARE @SQLString NVARCHAR(MAX)

	--GET Trade Month start date and end date
	SET @DtToday=CAST(CAST(YEAR(GETDATE()) AS CHAR(4)) + '-' + CAST(MONTH(GETDATE()) AS CHAR(2)) + '-' + CAST(DAY(GETDATE()) AS CHAR(2)) AS SMALLDATETIME)

	SELECT @YrStart = [year], @QtStart = quarter
		FROM dbo.CloseDate WHERE @DtToday BETWEEN StartDate AND EndDate

	DECLARE @QtCondition NVARCHAR(500)
	DECLARE @AfterCondition NVARCHAR(500)
	Declare @CommonCondition nvarchar(500)
	set @CommonCondition = ' 1=1 '

	SET @QtCondition=' TradeYear=' + CAST(@YrStart AS VARCHAR(4)) + '  AND TradeQuater=' + CAST(@QtStart AS VARCHAR(1))


	SET @AfterCondition='(  ( TradeYear=' + CAST(@YrStart AS VARCHAR(4)) + ' and TradeQuater> ' + CAST(@QtStart AS VARCHAR(1)) + ') or TradeYear>' +  CAST(@YrStart AS VARCHAR(4)) + ' ) '

	
	--GET Exchange Rate
	SELECT TOP 1 @ExRate=[Rate] FROM dbo.ExchangeRate ORDER BY UpdateDate DESC


	--GET Date list for Report date dimentions
	SET @SQLString='SELECT '+ CAST(@YrStart AS VARCHAR(4)) +' as RefYear, '+ CAST(@QtStart AS VARCHAR(1)) +' as RefQuater, null as RefMonth '
		+ ' union all select 2038, null, null'
		+ ' union all select 2046, null, null'


	EXEC sp_executesql @SQLString

	--Add BD and Prov Filter
	IF NOT EXISTS (SELECT a.* FROM dbo.UserRole a INNER JOIN dbo.Role b ON a.RoleID=b.RoleID WHERE b.RoleName='Admin' AND  a.EmployeeID=@EmployeeID)
	BEGIN
		SET @QtCondition=@QtCondition + ' AND BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		SET @AfterCondition=@AfterCondition + ' AND BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		set @CommonCondition = @CommonCondition  + ' AND BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		SET @QtCondition=@QtCondition + ' AND ProvinceID IN (SELECT ProvinceID FROM dbo.ProvinceAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		SET @AfterCondition=@AfterCondition + ' AND ProvinceID IN (SELECT ProvinceID FROM dbo.ProvinceAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		set @CommonCondition = @CommonCondition + ' AND ProvinceID IN (SELECT ProvinceID FROM dbo.ProvinceAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
	END

	IF @ASBOrLucent<>'2'
	BEGIN
		SET @QtCondition= @QtCondition + ' AND ASBorLucent='+ @ASBOrLucent
		SET @AfterCondition= @AfterCondition + ' AND ASBorLucent='+ @ASBOrLucent
		set @CommonCondition = @CommonCondition + ' AND ASBorLucent='+ @ASBOrLucent
	END

	--PRINT @DtCondition
	--GET Report Values
	--UNION Aggregated value by Year Quater Month, By Year Quater, By Year
	SET @SQLString='SELECT CATID,NULL AS NoUse,
		TradeYear,TradeQuater,Null as TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact '
		+ ' WHERE ' + @QtCondition + 
		' GROUP BY  CATID,TradeYear,TradeQuater

		UNION ALL 

		SELECT CATID,NULL AS NoUse,
		2038 as TradeYear,NULL AS TradeQuater,NULL AS TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact '
		+ ' WHERE ' + @AfterCondition + 
		' GROUP BY  CATID

		UNION ALL 

		SELECT CATID,NULL AS NoUse,
		2046 AS TradeYear ,NULL AS TradeQuater,NULL AS TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact'
		+ ' WHERE  ' + @CommonCondition + 
		' GROUP BY  CATID'


--select 	@SQLString
	EXEC sp_executesql @SQLString

	SET NOCOUNT OFF;
END

GO

/****** Object:  StoredProcedure [dbo].[sp_GetReportByCATByBD]    Script Date: 07/03/2009 13:54:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Joan.lu>
-- Create date: <2008-09-22>
-- Description:	<For Get ReportValues>
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetReportByCATByBD]
	@NoOfMonth INT, 
	@EmployeeID INT,
	@ASBOrLucent CHAR(1) = ''--0-ASB,1-Lucent,2-ALL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	--CHECK IF Employee has permission
	DECLARE @EntityType TINYINT

	IF @ASBOrLucent=''
	BEGIN
		SET @EntityType=2 --ALL
		SET @ASBOrLucent='2' --ALL
	END
	ELSE 
		SET @EntityType=CAST(@ASBOrLucent AS TINYINT)
			
	--IF NOT EXISTS (SELECT EmployeeID FROM dbo.Employee WHERE EmployeeID=@EmployeeID AND (ASBOrLucent=0 OR ASBOrLucent=@EntityType))
	--	RETURN 100


	DECLARE @DtToday SMALLDATETIME
	DECLARE @DtStart SMALLDATETIME
	DECLARE @DtEnd SMALLDATETIME
	DECLARE @DtCondition NVARCHAR(500)

	DECLARE @ExRate DECIMAL(8,6)
	DECLARE @SQLString NVARCHAR(MAX)

	--GET Trade Month start date and end date
	SET @DtToday=CAST(CAST(YEAR(GETDATE()) AS CHAR(4)) + '-' + CAST(MONTH(GETDATE()) AS CHAR(2)) + '-' + CAST(DAY(GETDATE()) AS CHAR(2)) AS SMALLDATETIME)

	SELECT @DtStart=CAST(CAST([Year] AS CHAR(4)) + '-' + CAST([Month] AS CHAR(2)) + '-01' AS SMALLDATETIME)
		FROM dbo.CloseDate WHERE @DtToday BETWEEN StartDate AND EndDate
	/*
	IF @DtStart IS NULL
	BEGIN
		--SET @StrError='本月关帐日期未设置!Close Date is not set for current month!'
		RETURN 101
	END
	*/	
	SET @DtEnd=DATEADD(mm,@NoOfMonth,@DtStart);
	SET @DtEnd=DATEADD(dd,-1,@DtEnd);
	--PRINT @DtEnd


	SET @DtCondition=' BETWEEN ''' + CAST(@DtStart AS VARCHAR(20)) + ''' AND ''' + CAST(@DtEnd AS VARCHAR(20)) + ''' ' 
	DECLARE @YrCondition NVARCHAR(500)
	SET @YrCondition=' TradeYear>=' + CAST(YEAR(@DtStart) AS VARCHAR(4)) + ' AND TradeYear<=' + CAST(YEAR(@DtEnd) AS VARCHAR(4))

	--GET Exchange Rate
	SELECT TOP 1 @ExRate=[Rate] FROM dbo.ExchangeRate ORDER BY UpdateDate DESC

	Declare @CommonCondition nvarchar(500)
	set @CommonCondition = ' 1=1 '
	--GET Date list for Report date dimentions
	SET @SQLString='SELECT RefYear, RefQuater,RefMonth FROM dbo.SysRefDate '
		+ ' WHERE RefDate ' + @DtCondition + ' GROUP BY RefYear,RefQuater ,RefMonth WITH ROLLUP'
		+ ' union all select 2046, null, null'

	EXEC sp_executesql @SQLString

	--Add Prov Filter
	IF NOT EXISTS (SELECT a.* FROM dbo.UserRole a INNER JOIN dbo.Role b ON a.RoleID=b.RoleID WHERE b.RoleName='Admin' AND  a.EmployeeID=@EmployeeID)
	BEGIN
		--SET @DtCondition=@DtCondition + ' AND BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		--SET @YrCondition=@YrCondition + ' AND BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		--SET @DtCondition=@DtCondition + ' AND CATID IN (SELECT CATID FROM dbo.CATAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		--SET @YrCondition=@YrCondition + ' AND CATID IN (SELECT CATID FROM dbo.CATAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		SET @DtCondition=@DtCondition + ' AND ProvinceID IN (SELECT ProvinceID FROM dbo.ProvinceAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		SET @YrCondition=@YrCondition + ' AND ProvinceID IN (SELECT ProvinceID FROM dbo.ProvinceAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		set @CommonCondition = @CommonCondition + ' AND ProvinceID IN (SELECT ProvinceID FROM dbo.ProvinceAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
	END
	IF @ASBOrLucent<>'2'
	BEGIN
		SET @DtCondition= @DtCondition + ' AND ASBorLucent='+ @ASBOrLucent
		SET @YrCondition= @YrCondition + ' AND ASBorLucent='+ @ASBOrLucent
		set @CommonCondition = @CommonCondition + ' AND ASBorLucent='+ @ASBOrLucent
	END

	--GET Report Values
	--UNION Aggregated value by Year Quater Month, By Year Quater, By Year
	SET @SQLString='SELECT CATID,BDID,
		TradeYear,TradeQuater,TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact '
		+ ' WHERE TradeDate ' + @DtCondition + 
		' GROUP BY  CATID,BDID,TradeYear,TradeQuater,TradeMonth

		UNION ALL 

		SELECT CATID,BDID,
		TradeYear,TradeQuater,NULL AS TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact '
		+ ' WHERE ' + @YrCondition + 
		' GROUP BY  CATID,BDID,TradeYear,TradeQuater	


		UNION ALL 

		SELECT CATID,BDID,
		TradeYear,NULL AS TradeQuater,NULL AS TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact'
		+ ' WHERE ' + @YrCondition + 
		' GROUP BY  CATID,BDID,TradeYear

		UNION ALL 

		SELECT CATID,BDID,
		2046 AS TradeYear,NULL AS TradeQuater,NULL AS TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact'
		+ ' WHERE ' + @CommonCondition + 
		' GROUP BY  CATID,BDID'
	
	EXEC sp_executesql @SQLString

	SET NOCOUNT OFF;
END

GO

/****** Object:  StoredProcedure [dbo].[sp_GetReportByCatByBD_Quarterly]    Script Date: 07/03/2009 13:54:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


create PROCEDURE [dbo].[sp_GetReportByCatByBD_Quarterly]
	@EmployeeID INT,
	@ASBOrLucent CHAR(1) = '' --0-ASB,1-Lucent,2-ALL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	IF @ASBOrLucent=''
	BEGIN
		SET @ASBOrLucent='2' --ALL
	END

	DECLARE @DtToday SMALLDATETIME
	DECLARE @YrStart int
	DECLARE @QtStart int



	DECLARE @ExRate DECIMAL(8,6)
	DECLARE @SQLString NVARCHAR(MAX)

	--GET Trade Month start date and end date
	SET @DtToday=CAST(CAST(YEAR(GETDATE()) AS CHAR(4)) + '-' + CAST(MONTH(GETDATE()) AS CHAR(2)) + '-' + CAST(DAY(GETDATE()) AS CHAR(2)) AS SMALLDATETIME)

	SELECT @YrStart = [year], @QtStart = quarter
		FROM dbo.CloseDate WHERE @DtToday BETWEEN StartDate AND EndDate

	DECLARE @QtCondition NVARCHAR(500)
	DECLARE @AfterCondition NVARCHAR(500)
	Declare @CommonCondition nvarchar(500)
	set @CommonCondition = ' 1=1 '

	SET @QtCondition=' TradeYear=' + CAST(@YrStart AS VARCHAR(4)) + '  AND TradeQuater=' + CAST(@QtStart AS VARCHAR(1))


	SET @AfterCondition='(  ( TradeYear=' + CAST(@YrStart AS VARCHAR(4)) + ' and TradeQuater> ' + CAST(@QtStart AS VARCHAR(1)) + ') or TradeYear>' +  CAST(@YrStart AS VARCHAR(4)) + ' ) '

	
	--GET Exchange Rate
	SELECT TOP 1 @ExRate=[Rate] FROM dbo.ExchangeRate ORDER BY UpdateDate DESC


	--GET Date list for Report date dimentions
	SET @SQLString='SELECT '+ CAST(@YrStart AS VARCHAR(4)) +' as RefYear, '+ CAST(@QtStart AS VARCHAR(1)) +' as RefQuater, null as RefMonth '
		+ ' union all select 2038, null, null'
		+ ' union all select 2046, null, null'


	EXEC sp_executesql @SQLString

	--Add BD and Prov Filter
	IF NOT EXISTS (SELECT a.* FROM dbo.UserRole a INNER JOIN dbo.Role b ON a.RoleID=b.RoleID WHERE b.RoleName='Admin' AND  a.EmployeeID=@EmployeeID)
	BEGIN
		SET @QtCondition=@QtCondition + ' AND ProvinceID IN (SELECT ProvinceID FROM dbo.ProvinceAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		SET @AfterCondition=@AfterCondition + ' AND ProvinceID IN (SELECT ProvinceID FROM dbo.ProvinceAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		set @CommonCondition = @CommonCondition + ' AND ProvinceID IN (SELECT ProvinceID FROM dbo.ProvinceAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
	END

	IF @ASBOrLucent<>'2'
	BEGIN
		SET @QtCondition= @QtCondition + ' AND ASBorLucent='+ @ASBOrLucent
		SET @AfterCondition= @AfterCondition + ' AND ASBorLucent='+ @ASBOrLucent
		set @CommonCondition = @CommonCondition + ' AND ASBorLucent='+ @ASBOrLucent
	END

	--PRINT @DtCondition
	--GET Report Values
	--UNION Aggregated value by Year Quater Month, By Year Quater, By Year
	SET @SQLString='SELECT CATID,BDID,
		TradeYear,TradeQuater,Null as TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
 		SUM(TransformedCount) TransformedCount,
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount
		FROM vw_PreContractFact '
		+ ' WHERE ' + @QtCondition + 
		' GROUP BY   CATID,BDID,TradeYear,TradeQuater

		UNION ALL 

		SELECT   CATID,BDID,
		2038 as TradeYear,NULL AS TradeQuater,NULL AS TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact '
		+ ' WHERE ' + @AfterCondition + 
		' GROUP BY  CATID,BDID

		UNION ALL 

		SELECT   CATID,BDID,
		2046 AS TradeYear ,NULL AS TradeQuater,NULL AS TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact'
		+ ' WHERE  ' + @CommonCondition + 
		' GROUP BY   CATID,BDID '


--select 	@SQLString
	EXEC sp_executesql @SQLString

	SET NOCOUNT OFF;
END

GO

/****** Object:  StoredProcedure [dbo].[sp_GetReportByProvByBD]    Script Date: 07/03/2009 13:54:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Joan.lu>
-- Create date: <2008-09-22>
-- Description:	<For Get ReportValues>
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetReportByProvByBD]
	@NoOfMonth INT, 
	@EmployeeID INT,
	@ASBOrLucent CHAR(1) = ''--0-ASB,1-Lucent,2-ALL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	--CHECK IF Employee has permission
	DECLARE @EntityType TINYINT

	IF @ASBOrLucent=''
	BEGIN
		SET @EntityType=2 --ALL
		SET @ASBOrLucent='2' --ALL
	END
	ELSE 
		SET @EntityType=CAST(@ASBOrLucent AS TINYINT)
			
	--IF NOT EXISTS (SELECT EmployeeID FROM dbo.Employee WHERE EmployeeID=@EmployeeID AND (ASBOrLucent=0 OR ASBOrLucent=@EntityType))
	--	RETURN 100


	DECLARE @DtToday SMALLDATETIME
	DECLARE @DtStart SMALLDATETIME
	DECLARE @DtEnd SMALLDATETIME
	DECLARE @DtCondition NVARCHAR(500)

	DECLARE @ExRate DECIMAL(8,6)
	DECLARE @SQLString NVARCHAR(MAX)

	--GET Trade Month start date and end date
	SET @DtToday=CAST(CAST(YEAR(GETDATE()) AS CHAR(4)) + '-' + CAST(MONTH(GETDATE()) AS CHAR(2)) + '-' + CAST(DAY(GETDATE()) AS CHAR(2)) AS SMALLDATETIME)

	SELECT @DtStart=CAST(CAST([Year] AS CHAR(4)) + '-' + CAST([Month] AS CHAR(2)) + '-01' AS SMALLDATETIME)
		FROM dbo.CloseDate WHERE @DtToday BETWEEN StartDate AND EndDate
	/*
	IF @DtStart IS NULL
	BEGIN
		--SET @StrError='本月关帐日期未设置!Close Date is not set for current month!'
		RETURN 101
	END
	*/

	SET @DtEnd=DATEADD(mm,@NoOfMonth,@DtStart);
	SET @DtEnd=DATEADD(dd,-1,@DtEnd);
	--PRINT @DtEnd


	SET @DtCondition=' BETWEEN ''' + CAST(@DtStart AS VARCHAR(20)) + ''' AND ''' + CAST(@DtEnd AS VARCHAR(20)) + ''' ' 
	DECLARE @YrCondition NVARCHAR(500)
	SET @YrCondition=' TradeYear>=' + CAST(YEAR(@DtStart) AS VARCHAR(4)) + ' AND TradeYear<=' + CAST(YEAR(@DtEnd) AS VARCHAR(4))

	--GET Exchange Rate
	SELECT TOP 1 @ExRate=[Rate] FROM dbo.ExchangeRate ORDER BY UpdateDate DESC

	Declare @CommonCondition nvarchar(500)
	set @CommonCondition = ' 1=1 '
	--GET Date list for Report date dimentions
	SET @SQLString='SELECT RefYear, RefQuater,RefMonth FROM dbo.SysRefDate '
		+ ' WHERE RefDate ' + @DtCondition + ' GROUP BY RefYear,RefQuater ,RefMonth WITH ROLLUP'
		+ ' union all select 2046, null, null'

	EXEC sp_executesql @SQLString

	--Add CAT Filter
	IF NOT EXISTS (SELECT a.* FROM dbo.UserRole a INNER JOIN dbo.Role b ON a.RoleID=b.RoleID WHERE b.RoleName='Admin' AND  a.EmployeeID=@EmployeeID)
	BEGIN
		--SET @DtCondition=@D		set @CommonCondition = @CommonCondition + ' AND ASBorLucent='+ @ASBOrLucenttCondition + ' AND BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		--SET @YrCondition=@YrCondition + ' AND BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		SET @DtCondition=@DtCondition + ' AND CATID IN (SELECT CATID FROM dbo.CATAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		SET @YrCondition=@YrCondition + ' AND CATID IN (SELECT CATID FROM dbo.CATAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		--SET @DtCondition=@DtCondition + ' AND ProvinceID IN (SELECT ProvinceID FROM dbo.ProvinceAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		--SET @YrCondition=@YrCondition + ' AND ProvinceID IN (SELECT ProvinceID FROM dbo.ProvinceAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		set @CommonCondition = @CommonCondition  + ' AND CATID IN (SELECT CATID FROM dbo.CATAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
	END
	IF @ASBOrLucent<>'2'
	BEGIN
		SET @DtCondition= @DtCondition + ' AND ASBorLucent='+ @ASBOrLucent
		SET @YrCondition= @YrCondition + ' AND ASBorLucent='+ @ASBOrLucent
		set @CommonCondition = @CommonCondition + ' AND ASBorLucent='+ @ASBOrLucent
	END

	--GET Report Values
	--UNION Aggregated value by Year Quater Month, By Year Quater, By Year
	SET @SQLString='SELECT ProvinceID,BDID,
		TradeYear,TradeQuater,TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact '
		+ ' WHERE TradeDate ' + @DtCondition + 
		' GROUP BY  ProvinceID,BDID,TradeYear,TradeQuater,TradeMonth

		UNION ALL 

		SELECT ProvinceID,BDID,
		TradeYear,TradeQuater,NULL AS TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact '
		+ ' WHERE ' + @YrCondition + 
		' GROUP BY  ProvinceID,BDID,TradeYear,TradeQuater	


		UNION ALL 

		SELECT ProvinceID,BDID,
		TradeYear,NULL AS TradeQuater,NULL AS TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact'
		+ ' WHERE ' + @YrCondition + 
		' GROUP BY  ProvinceID,BDID,TradeYear

		UNION ALL 

		SELECT ProvinceID,BDID,
		2046 AS TradeYear,NULL AS TradeQuater,NULL AS TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact'
		+ ' WHERE ' + @CommonCondition + 
		' GROUP BY  ProvinceID,BDID'
	
	EXEC sp_executesql @SQLString

	SET NOCOUNT OFF;
END

GO

/****** Object:  StoredProcedure [dbo].[sp_GetReportByProvByBD_Quarterly]    Script Date: 07/03/2009 13:54:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


create PROCEDURE [dbo].[sp_GetReportByProvByBD_Quarterly]
	@EmployeeID INT,
	@ASBOrLucent CHAR(1) = '' --0-ASB,1-Lucent,2-ALL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	IF @ASBOrLucent=''
	BEGIN
		SET @ASBOrLucent='2' --ALL
	END

	DECLARE @DtToday SMALLDATETIME
	DECLARE @YrStart int
	DECLARE @QtStart int



	DECLARE @ExRate DECIMAL(8,6)
	DECLARE @SQLString NVARCHAR(MAX)

	--GET Trade Month start date and end date
	SET @DtToday=CAST(CAST(YEAR(GETDATE()) AS CHAR(4)) + '-' + CAST(MONTH(GETDATE()) AS CHAR(2)) + '-' + CAST(DAY(GETDATE()) AS CHAR(2)) AS SMALLDATETIME)

	SELECT @YrStart = [year], @QtStart = quarter
		FROM dbo.CloseDate WHERE @DtToday BETWEEN StartDate AND EndDate

	DECLARE @QtCondition NVARCHAR(500)
	DECLARE @AfterCondition NVARCHAR(500)
	Declare @CommonCondition nvarchar(500)
	set @CommonCondition = ' 1=1 '

	SET @QtCondition=' TradeYear=' + CAST(@YrStart AS VARCHAR(4)) + '  AND TradeQuater=' + CAST(@QtStart AS VARCHAR(1))


	SET @AfterCondition='(  ( TradeYear=' + CAST(@YrStart AS VARCHAR(4)) + ' and TradeQuater> ' + CAST(@QtStart AS VARCHAR(1)) + ') or TradeYear>' +  CAST(@YrStart AS VARCHAR(4)) + ' ) '

	
	--GET Exchange Rate
	SELECT TOP 1 @ExRate=[Rate] FROM dbo.ExchangeRate ORDER BY UpdateDate DESC


	--GET Date list for Report date dimentions
	SET @SQLString='SELECT '+ CAST(@YrStart AS VARCHAR(4)) +' as RefYear, '+ CAST(@QtStart AS VARCHAR(1)) +' as RefQuater, null as RefMonth '
		+ ' union all select 2038, null, null'
		+ ' union all select 2046, null, null'


	EXEC sp_executesql @SQLString

	--Add BD and Prov Filter
	IF NOT EXISTS (SELECT a.* FROM dbo.UserRole a INNER JOIN dbo.Role b ON a.RoleID=b.RoleID WHERE b.RoleName='Admin' AND  a.EmployeeID=@EmployeeID)
	BEGIN
		SET @QtCondition=@QtCondition + ' AND CATID IN (SELECT CATID FROM dbo.CATAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		SET @AfterCondition=@AfterCondition + ' AND CATID IN (SELECT CATID FROM dbo.CATAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		set @CommonCondition = @CommonCondition + ' AND CATID IN (SELECT CATID FROM dbo.CATAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
	END

	IF @ASBOrLucent<>'2'
	BEGIN
		SET @QtCondition= @QtCondition + ' AND ASBorLucent='+ @ASBOrLucent
		SET @AfterCondition= @AfterCondition + ' AND ASBorLucent='+ @ASBOrLucent
		set @CommonCondition = @CommonCondition + ' AND ASBorLucent='+ @ASBOrLucent
	END

	--PRINT @DtCondition
	--GET Report Values
	--UNION Aggregated value by Year Quater Month, By Year Quater, By Year
	SET @SQLString='SELECT ProvinceID,BDID,
		TradeYear,TradeQuater,Null as TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
 		SUM(TransformedCount) TransformedCount,
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount
		FROM vw_PreContractFact '
		+ ' WHERE ' + @QtCondition + 
		' GROUP BY   ProvinceID,BDID,TradeYear,TradeQuater

		UNION ALL 

		SELECT   ProvinceID,BDID,
		2038 as TradeYear,NULL AS TradeQuater,NULL AS TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact '
		+ ' WHERE ' + @AfterCondition + 
		' GROUP BY  ProvinceID,BDID

		UNION ALL 

		SELECT   ProvinceID,BDID,
		2046 AS TradeYear ,NULL AS TradeQuater,NULL AS TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact'
		+ ' WHERE  ' + @CommonCondition + 
		' GROUP BY   ProvinceID,BDID '


--select 	@SQLString
	EXEC sp_executesql @SQLString

	SET NOCOUNT OFF;
END

GO

/****** Object:  StoredProcedure [dbo].[sp_GetReportByProvByCAT]    Script Date: 07/03/2009 13:54:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Joan.lu>
-- Create date: <2008-09-19>
-- Description:	<For Get ReportValues>
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetReportByProvByCAT]
	@NoOfMonth INT, 
	@EmployeeID INT,
	@ASBOrLucent CHAR(1) = ''--0-ASB,1-Lucent,2-ALL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	--CHECK IF Employee has permission
	DECLARE @EntityType TINYINT

	IF @ASBOrLucent=''
	BEGIN
		SET @EntityType=2 --ALL
		SET @ASBOrLucent='2' --ALL
	END

	ELSE 
		SET @EntityType=CAST(@ASBOrLucent AS TINYINT)
			
	--IF NOT EXISTS (SELECT EmployeeID FROM dbo.Employee WHERE EmployeeID=@EmployeeID AND (ASBOrLucent=0 OR ASBOrLucent=@EntityType))
	--	RETURN 100


	DECLARE @DtToday SMALLDATETIME
	DECLARE @DtStart SMALLDATETIME
	DECLARE @DtEnd SMALLDATETIME
	DECLARE @DtCondition NVARCHAR(500)

	DECLARE @ExRate DECIMAL(8,6)
	DECLARE @SQLString NVARCHAR(MAX)

	--GET Trade Month start date and end date
	SET @DtToday=CAST(CAST(YEAR(GETDATE()) AS CHAR(4)) + '-' + CAST(MONTH(GETDATE()) AS CHAR(2)) + '-' + CAST(DAY(GETDATE()) AS CHAR(2)) AS SMALLDATETIME)

	SELECT @DtStart=CAST(CAST([Year] AS CHAR(4)) + '-' + CAST([Month] AS CHAR(2)) + '-01' AS SMALLDATETIME)
		FROM dbo.CloseDate WHERE @DtToday BETWEEN StartDate AND EndDate
	/*
	IF @DtStart IS NULL
	BEGIN
		--SET @StrError='本月关帐日期未设置!Close Date is not set for current month!'
		RETURN 101
	END
	*/
	SET @DtEnd=DATEADD(mm,@NoOfMonth,@DtStart);
	SET @DtEnd=DATEADD(dd,-1,@DtEnd);
	--PRINT @DtEnd


	SET @DtCondition=' BETWEEN ''' + CAST(@DtStart AS VARCHAR(20)) + ''' AND ''' + CAST(@DtEnd AS VARCHAR(20)) + ''' ' 
	DECLARE @YrCondition NVARCHAR(500)
	SET @YrCondition=' TradeYear>=' + CAST(YEAR(@DtStart) AS VARCHAR(4)) + ' AND TradeYear<=' + CAST(YEAR(@DtEnd) AS VARCHAR(4))
	--GET Exchange Rate
	SELECT TOP 1 @ExRate=[Rate] FROM dbo.ExchangeRate ORDER BY UpdateDate DESC

	Declare @CommonCondition nvarchar(500)
	set @CommonCondition = ' 1=1 '
	--GET Date list for Report date dimentions
	SET @SQLString='SELECT RefYear, RefQuater,RefMonth FROM dbo.SysRefDate '
		+ ' WHERE RefDate ' + @DtCondition + ' GROUP BY RefYear,RefQuater ,RefMonth WITH ROLLUP'
		+ ' union all select 2046, null, null'
--	PRINT @SQLString

	EXEC sp_executesql @SQLString
	--Add BD Filter
	IF NOT EXISTS (SELECT a.* FROM dbo.UserRole a INNER JOIN dbo.Role b ON a.RoleID=b.RoleID WHERE b.RoleName='Admin' AND  a.EmployeeID=@EmployeeID)
	BEGIN
		SET @DtCondition=@DtCondition + ' AND BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		SET @YrCondition=@YrCondition + ' AND BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		--SET @DtCondition=@DtCondition + ' AND CATID IN (SELECT CATID FROM dbo.CATAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		--SET @YrCondition=@YrCondition + ' AND CATID IN (SELECT CATID FROM dbo.CATAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		--SET @DtCondition=@DtCondition + ' AND ProvinceID IN (SELECT ProvinceID FROM dbo.ProvinceAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		--SET @YrCondition=@YrCondition + ' AND ProvinceID IN (SELECT ProvinceID FROM dbo.ProvinceAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		set @CommonCondition = @CommonCondition  + ' AND BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
	END
	IF @ASBOrLucent<>'2'
	BEGIN
		SET @DtCondition= @DtCondition + ' AND ASBorLucent='+ @ASBOrLucent
		SET @YrCondition= @YrCondition + ' AND ASBorLucent='+ @ASBOrLucent
		set @CommonCondition = @CommonCondition + ' AND ASBorLucent='+ @ASBOrLucent
	END

	--GET Report Values
	--UNION Aggregated value by Year Quater Month, By Year Quater, By Year
	SET @SQLString='SELECT ProvinceID,CATID,
		TradeYear,TradeQuater,TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact '
		+ ' WHERE TradeDate ' + @DtCondition + 
		' GROUP BY  ProvinceID,CATID,TradeYear,TradeQuater,TradeMonth

		UNION ALL 

		SELECT ProvinceID,CATID,
		TradeYear,TradeQuater,NULL AS TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact '
		+ ' WHERE ' + @YrCondition + 
		' GROUP BY  ProvinceID,CATID,TradeYear,TradeQuater	


		UNION ALL 

		SELECT ProvinceID,CATID,
		TradeYear,NULL AS TradeQuater,NULL AS TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact'
		+ ' WHERE ' + @YrCondition + 
		' GROUP BY  ProvinceID,CATID,TradeYear

		UNION ALL 

		SELECT ProvinceID,CATID,
		2046 AS TradeYear,NULL AS TradeQuater,NULL AS TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact'
		+ ' WHERE ' + @CommonCondition + 
		' GROUP BY  ProvinceID,CATID'
	
	EXEC sp_executesql @SQLString

	SET NOCOUNT OFF;
END

GO

/****** Object:  StoredProcedure [dbo].[sp_GetReportByProvByCAT_Quarterly]    Script Date: 07/03/2009 13:54:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


create PROCEDURE [dbo].[sp_GetReportByProvByCAT_Quarterly]
	@EmployeeID INT,
	@ASBOrLucent CHAR(1) = '' --0-ASB,1-Lucent,2-ALL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	IF @ASBOrLucent=''
	BEGIN
		SET @ASBOrLucent='2' --ALL
	END

	DECLARE @DtToday SMALLDATETIME
	DECLARE @YrStart int
	DECLARE @QtStart int



	DECLARE @ExRate DECIMAL(8,6)
	DECLARE @SQLString NVARCHAR(MAX)

	--GET Trade Month start date and end date
	SET @DtToday=CAST(CAST(YEAR(GETDATE()) AS CHAR(4)) + '-' + CAST(MONTH(GETDATE()) AS CHAR(2)) + '-' + CAST(DAY(GETDATE()) AS CHAR(2)) AS SMALLDATETIME)

	SELECT @YrStart = [year], @QtStart = quarter
		FROM dbo.CloseDate WHERE @DtToday BETWEEN StartDate AND EndDate

	DECLARE @QtCondition NVARCHAR(500)
	DECLARE @AfterCondition NVARCHAR(500)
	Declare @CommonCondition nvarchar(500)
	set @CommonCondition = ' 1=1 '

	SET @QtCondition=' TradeYear=' + CAST(@YrStart AS VARCHAR(4)) + '  AND TradeQuater=' + CAST(@QtStart AS VARCHAR(1))


	SET @AfterCondition='(  ( TradeYear=' + CAST(@YrStart AS VARCHAR(4)) + ' and TradeQuater> ' + CAST(@QtStart AS VARCHAR(1)) + ') or TradeYear>' +  CAST(@YrStart AS VARCHAR(4)) + ' ) '

	
	--GET Exchange Rate
	SELECT TOP 1 @ExRate=[Rate] FROM dbo.ExchangeRate ORDER BY UpdateDate DESC


	--GET Date list for Report date dimentions
	SET @SQLString='SELECT '+ CAST(@YrStart AS VARCHAR(4)) +' as RefYear, '+ CAST(@QtStart AS VARCHAR(1)) +' as RefQuater, null as RefMonth '
		+ ' union all select 2038, null, null'
		+ ' union all select 2046, null, null'


	EXEC sp_executesql @SQLString

	--Add BD and Prov Filter
	IF NOT EXISTS (SELECT a.* FROM dbo.UserRole a INNER JOIN dbo.Role b ON a.RoleID=b.RoleID WHERE b.RoleName='Admin' AND  a.EmployeeID=@EmployeeID)
	BEGIN
		SET @QtCondition=@QtCondition + ' AND BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		SET @AfterCondition=@AfterCondition + ' AND BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		set @CommonCondition = @CommonCondition + ' AND BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
	END

	IF @ASBOrLucent<>'2'
	BEGIN
		SET @QtCondition= @QtCondition + ' AND ASBorLucent='+ @ASBOrLucent
		SET @AfterCondition= @AfterCondition + ' AND ASBorLucent='+ @ASBOrLucent
		set @CommonCondition = @CommonCondition + ' AND ASBorLucent='+ @ASBOrLucent
	END

	--PRINT @DtCondition
	--GET Report Values
	--UNION Aggregated value by Year Quater Month, By Year Quater, By Year
	SET @SQLString='SELECT ProvinceID,CATID,
		TradeYear,TradeQuater,Null as TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
 		SUM(TransformedCount) TransformedCount,
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount
		FROM vw_PreContractFact '
		+ ' WHERE ' + @QtCondition + 
		' GROUP BY   ProvinceID,CATID,TradeYear,TradeQuater

		UNION ALL 

		SELECT   ProvinceID,CATID,
		2038 as TradeYear,NULL AS TradeQuater,NULL AS TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact '
		+ ' WHERE ' + @AfterCondition + 
		' GROUP BY  ProvinceID,CATID

		UNION ALL 

		SELECT   ProvinceID,CATID,
		2046 AS TradeYear ,NULL AS TradeQuater,NULL AS TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact'
		+ ' WHERE  ' + @CommonCondition + 
		' GROUP BY  ProvinceID,CATID '


--select 	@SQLString
	EXEC sp_executesql @SQLString

	SET NOCOUNT OFF;
END

GO

/****** Object:  StoredProcedure [dbo].[sp_GetReportByProvince]    Script Date: 07/03/2009 13:54:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Joan.lu>
-- Create date: <2008-09-22>
-- Description:	<For Get ReportValues>
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetReportByProvince]
	@NoOfMonth INT, 
	@EmployeeID INT,
	@ASBOrLucent CHAR(1) = ''--0-ASB,1-Lucent,2-ALL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	--CHECK IF Employee has permission
	DECLARE @EntityType TINYINT

	IF @ASBOrLucent=''
	BEGIN
		SET @EntityType=2 --ALL
		SET @ASBOrLucent='2' --ALL
	END
	ELSE 
		SET @EntityType=CAST(@ASBOrLucent AS TINYINT)
			
	--IF NOT EXISTS (SELECT EmployeeID FROM dbo.Employee WHERE EmployeeID=@EmployeeID AND (ASBOrLucent=0 OR ASBOrLucent=@EntityType))
	--	RETURN 100


	DECLARE @DtToday SMALLDATETIME
	DECLARE @DtStart SMALLDATETIME
	DECLARE @DtEnd SMALLDATETIME
	DECLARE @DtCondition NVARCHAR(500)

	DECLARE @ExRate DECIMAL(8,6)
	DECLARE @SQLString NVARCHAR(MAX)

	--GET Trade Month start date and end date
	SET @DtToday=CAST(CAST(YEAR(GETDATE()) AS CHAR(4)) + '-' + CAST(MONTH(GETDATE()) AS CHAR(2)) + '-' + CAST(DAY(GETDATE()) AS CHAR(2)) AS SMALLDATETIME)

	SELECT @DtStart=CAST(CAST([Year] AS CHAR(4)) + '-' + CAST([Month] AS CHAR(2)) + '-01' AS SMALLDATETIME)
		FROM dbo.CloseDate WHERE @DtToday BETWEEN StartDate AND EndDate
	/*
	IF @DtStart IS NULL
	BEGIN
		--SET @StrError='本月关帐日期未设置!Close Date is not set for current month!'
		RETURN 101
	END
	*/

	SET @DtEnd=DATEADD(mm,@NoOfMonth,@DtStart);
	SET @DtEnd=DATEADD(dd,-1,@DtEnd);
	--PRINT @DtEnd


	SET @DtCondition=' BETWEEN ''' + CAST(@DtStart AS VARCHAR(20)) + ''' AND ''' + CAST(@DtEnd AS VARCHAR(20)) + ''' ' 
	DECLARE @YrCondition NVARCHAR(500)
	SET @YrCondition=' TradeYear>=' + CAST(YEAR(@DtStart) AS VARCHAR(4)) + ' AND TradeYear<=' + CAST(YEAR(@DtEnd) AS VARCHAR(4))

	--GET Exchange Rate
	SELECT TOP 1 @ExRate=[Rate] FROM dbo.ExchangeRate ORDER BY UpdateDate DESC

	Declare @CommonCondition nvarchar(500)
	set @CommonCondition = ' 1=1 '
	--GET Date list for Report date dimentions
	SET @SQLString='SELECT RefYear, RefQuater,RefMonth FROM dbo.SysRefDate '
		+ ' WHERE RefDate ' + @DtCondition + ' GROUP BY RefYear,RefQuater ,RefMonth WITH ROLLUP'
		+ ' union all select 2046 as RefYear, null as RefQuater, null as RefMonth'

	EXEC sp_executesql @SQLString
	--Add BD and CAT Filter
	IF NOT EXISTS (SELECT a.* FROM dbo.UserRole a INNER JOIN dbo.Role b ON a.RoleID=b.RoleID WHERE b.RoleName='Admin' AND  a.EmployeeID=@EmployeeID)
	BEGIN
		SET @DtCondition=@DtCondition + ' AND BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		SET @YrCondition=@YrCondition + ' AND BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		SET @DtCondition=@DtCondition + ' AND CATID IN (SELECT CATID FROM dbo.CATAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		SET @YrCondition=@YrCondition + ' AND CATID IN (SELECT CATID FROM dbo.CATAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		--SET @DtCondition=@DtCondition + ' AND ProvinceID IN (SELECT ProvinceID FROM dbo.ProvinceAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		--SET @YrCondition=@YrCondition + ' AND ProvinceID IN (SELECT ProvinceID FROM dbo.ProvinceAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		set @CommonCondition = @CommonCondition + ' AND BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		set @CommonCondition = @CommonCondition + ' AND CATID IN (SELECT CATID FROM dbo.CATAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
	END
	IF @ASBOrLucent<>'2'
	BEGIN
		SET @DtCondition= @DtCondition + ' AND ASBorLucent='+ @ASBOrLucent
		SET @YrCondition= @YrCondition + ' AND ASBorLucent='+ @ASBOrLucent
		set @CommonCondition = @CommonCondition + ' AND ASBorLucent='+ @ASBOrLucent
	END

	--GET Report Values
	--UNION Aggregated value by Year Quater Month, By Year Quater, By Year
	SET @SQLString='SELECT ProvinceID,NULL AS NoUse,
		TradeYear,TradeQuater,TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact '
		+ ' WHERE TradeDate ' + @DtCondition + 
		' GROUP BY  ProvinceID,TradeYear,TradeQuater,TradeMonth

		UNION ALL 

		SELECT ProvinceID,NULL AS NoUse,
		TradeYear,TradeQuater,NULL AS TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact '
		+ ' WHERE ' + @YrCondition + 
		' GROUP BY  ProvinceID,TradeYear,TradeQuater	


		UNION ALL 

		SELECT ProvinceID,NULL AS NoUse,
		TradeYear,NULL AS TradeQuater,NULL AS TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact'
		+ ' WHERE ' + @YrCondition + 
		' GROUP BY  ProvinceID,TradeYear

		UNION ALL 

		SELECT ProvinceID,NULL AS NoUse,
		2046 AS TradeYear,NULL AS TradeQuater,NULL AS TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact'
		+ ' WHERE ' + @CommonCondition + 
		' GROUP BY  ProvinceID'
	
	EXEC sp_executesql @SQLString

	SET NOCOUNT OFF;
END

GO

/****** Object:  StoredProcedure [dbo].[sp_GetReportByProvince_Quarterly]    Script Date: 07/03/2009 13:54:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


create PROCEDURE [dbo].[sp_GetReportByProvince_Quarterly]
	@EmployeeID INT,
	@ASBOrLucent CHAR(1) = '' --0-ASB,1-Lucent,2-ALL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	IF @ASBOrLucent=''
	BEGIN
		SET @ASBOrLucent='2' --ALL
	END

	DECLARE @DtToday SMALLDATETIME
	DECLARE @YrStart int
	DECLARE @QtStart int



	DECLARE @ExRate DECIMAL(8,6)
	DECLARE @SQLString NVARCHAR(MAX)

	--GET Trade Month start date and end date
	SET @DtToday=CAST(CAST(YEAR(GETDATE()) AS CHAR(4)) + '-' + CAST(MONTH(GETDATE()) AS CHAR(2)) + '-' + CAST(DAY(GETDATE()) AS CHAR(2)) AS SMALLDATETIME)

	SELECT @YrStart = [year], @QtStart = quarter
		FROM dbo.CloseDate WHERE @DtToday BETWEEN StartDate AND EndDate

	DECLARE @QtCondition NVARCHAR(500)
	DECLARE @AfterCondition NVARCHAR(500)
	Declare @CommonCondition nvarchar(500)
	set @CommonCondition = ' 1=1 '

	SET @QtCondition=' TradeYear=' + CAST(@YrStart AS VARCHAR(4)) + '  AND TradeQuater=' + CAST(@QtStart AS VARCHAR(1))


	SET @AfterCondition='(  ( TradeYear=' + CAST(@YrStart AS VARCHAR(4)) + ' and TradeQuater> ' + CAST(@QtStart AS VARCHAR(1)) + ') or TradeYear>' +  CAST(@YrStart AS VARCHAR(4)) + ' ) '

	
	--GET Exchange Rate
	SELECT TOP 1 @ExRate=[Rate] FROM dbo.ExchangeRate ORDER BY UpdateDate DESC


	--GET Date list for Report date dimentions
	SET @SQLString='SELECT '+ CAST(@YrStart AS VARCHAR(4)) +' as RefYear, '+ CAST(@QtStart AS VARCHAR(1)) +' as RefQuater, null as RefMonth '
		+ ' union all select 2038, null, null'
		+ ' union all select 2046, null, null'


	EXEC sp_executesql @SQLString

	--Add BD and Prov Filter
	IF NOT EXISTS (SELECT a.* FROM dbo.UserRole a INNER JOIN dbo.Role b ON a.RoleID=b.RoleID WHERE b.RoleName='Admin' AND  a.EmployeeID=@EmployeeID)
	BEGIN
		SET @QtCondition=@QtCondition + ' AND BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		SET @AfterCondition=@AfterCondition + ' AND BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		set @CommonCondition = @CommonCondition + ' AND BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		SET @QtCondition=@QtCondition + ' AND CATID IN (SELECT CATID FROM dbo.CATAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		SET @AfterCondition=@AfterCondition + ' AND CATID IN (SELECT CATID FROM dbo.CATAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
		set @CommonCondition = @CommonCondition + ' AND CATID IN (SELECT CATID FROM dbo.CATAssignment WHERE EmployeeID=' + CAST(@EmployeeID AS VARCHAR(10)) + ')'
	END

	IF @ASBOrLucent<>'2'
	BEGIN
		SET @QtCondition= @QtCondition + ' AND ASBorLucent='+ @ASBOrLucent
		SET @AfterCondition= @AfterCondition + ' AND ASBorLucent='+ @ASBOrLucent
		set @CommonCondition = @CommonCondition + ' AND ASBorLucent='+ @ASBOrLucent
	END

	--PRINT @DtCondition
	--GET Report Values
	--UNION Aggregated value by Year Quater Month, By Year Quater, By Year
	SET @SQLString='SELECT ProvinceID,NULL AS NoUse,
		TradeYear,TradeQuater,Null as TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
 		SUM(TransformedCount) TransformedCount,
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount
		FROM vw_PreContractFact '
		+ ' WHERE ' + @QtCondition + 
		' GROUP BY   ProvinceID,TradeYear,TradeQuater

		UNION ALL 

		SELECT   ProvinceID ,NULL AS NoUse,
		2038 as TradeYear,NULL AS TradeQuater,NULL AS TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact '
		+ ' WHERE ' + @AfterCondition + 
		' GROUP BY  ProvinceID 

		UNION ALL 

		SELECT   ProvinceID ,NULL AS NoUse,
		2046 AS TradeYear ,NULL AS TradeQuater,NULL AS TradeMonth,
		SUM(ROUND(TransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Transformed,
		SUM(ROUND(ToBeTransformedRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS ToBeTransformed,
		SUM(ROUND(DeliveredRMB/1.17/' + CAST(@ExRate AS NVARCHAR(9)) + '/1000000,4)) AS Delivered,
		SUM(TransformedCount) TransformedCount,		
		SUM(ToBeTransformedCount) ToBeTransformedCount,
		SUM(DeliveredCount) DeliveredCount  
		FROM vw_PreContractFact'
		+ ' WHERE  ' + @CommonCondition + 
		' GROUP BY  ProvinceID '


--select 	@SQLString
	EXEC sp_executesql @SQLString

	SET NOCOUNT OFF;
END

GO

/****** Object:  StoredProcedure [dbo].[sp_GetRiskProjectReport]    Script Date: 07/03/2009 13:54:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Joan.lu>
-- Create date: <2008-09-24>
-- Description:	<For Get Risk Project Report>
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetRiskProjectReport]
@Year INT,
@Quater INT,
@EmployeeID INT
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @ExRate DECIMAL(8,6)

	--GET Exchange Rate
	SELECT TOP 1 @ExRate=[Rate] FROM dbo.ExchangeRate ORDER BY UpdateDate DESC
	DECLARE @ASBOrLucent INT
	SELECT @ASBOrLucent=ASBOrLucent FROM dbo.Employee WHERE EmployeeID=@EmployeeID

	--Filter By BD
	IF NOT EXISTS (SELECT a.* FROM dbo.UserRole a INNER JOIN dbo.Role b ON a.RoleID=b.RoleID WHERE b.RoleName='Admin' AND  a.EmployeeID=@EmployeeID)
	BEGIN
		IF @ASBOrLucent=2
		BEGIN
			SELECT ProvinceID,CATID, 'Secure' AS SecureOrRisk,
				SUM(ROUND(ToBeTransformedRMB/1.17/@ExRate/1000000,4)) AS ToBeTransformed,
				SUM(ROUND(DeliveredRMB/1.17/@ExRate/1000000,4)) AS Delivered,
				SUM(ToBeTransformedCount) ToBeTransformedCount,
				SUM(DeliveredCount) DeliveredCount
			FROM dbo.vw_PreContractFact
				WHERE (StatusID=10 OR StatusID=11) AND 
					(TradeYear=@Year AND TradeQuater=@Quater) AND IsRisk=0
					AND BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=@EmployeeID)
				GROUP BY ProvinceID,CATID
			UNION ALL
			SELECT ProvinceID,CATID, 'Risk' AS SecureOrRisk,
				SUM(ROUND(ToBeTransformedRMB/1.17/@ExRate/1000000,4)) AS ToBeTransformed,
				SUM(ROUND(DeliveredRMB/1.17/@ExRate/1000000,4)) AS Delivered,
				SUM(ToBeTransformedCount) ToBeTransformedCount,
				SUM(DeliveredCount) DeliveredCount
			FROM dbo.vw_PreContractFact
				WHERE (StatusID=10 OR StatusID=11) AND 
					(TradeYear=@Year AND TradeQuater=@Quater) AND IsRisk=1
					AND BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=@EmployeeID)
				GROUP BY ProvinceID,CATID
			UNION ALL
			SELECT ProvinceID,CATID, 'Total' AS SecureOrRisk,
				SUM(ROUND(ToBeTransformedRMB/1.17/@ExRate/1000000,4)) AS ToBeTransformed,
				SUM(ROUND(DeliveredRMB/1.17/@ExRate/1000000,4)) AS Delivered,
				SUM(ToBeTransformedCount) ToBeTransformedCount,
				SUM(DeliveredCount) DeliveredCount
			FROM dbo.vw_PreContractFact
				WHERE (StatusID=10 OR StatusID=11) AND 
					(TradeYear=@Year AND TradeQuater=@Quater)
					AND BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=@EmployeeID)
				GROUP BY ProvinceID,CATID
		END
		ELSE
		BEGIN
			SELECT ProvinceID,CATID, 'Secure' AS SecureOrRisk,
				SUM(ROUND(ToBeTransformedRMB/1.17/@ExRate/1000000,4)) AS ToBeTransformed,
				SUM(ROUND(DeliveredRMB/1.17/@ExRate/1000000,4)) AS Delivered,
				SUM(ToBeTransformedCount) ToBeTransformedCount,
				SUM(DeliveredCount) DeliveredCount
			FROM dbo.vw_PreContractFact
				WHERE (StatusID=10 OR StatusID=11) AND 
					(TradeYear=@Year AND TradeQuater=@Quater) AND IsRisk=0
					AND BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=@EmployeeID)
					AND ASBorLucent=@ASBorLucent
				GROUP BY ProvinceID,CATID
			UNION ALL
			SELECT ProvinceID,CATID, 'Risk' AS SecureOrRisk,
				SUM(ROUND(ToBeTransformedRMB/1.17/@ExRate/1000000,4)) AS ToBeTransformed,
				SUM(ROUND(DeliveredRMB/1.17/@ExRate/1000000,4)) AS Delivered,
				SUM(ToBeTransformedCount) ToBeTransformedCount,
				SUM(DeliveredCount) DeliveredCount
			FROM dbo.vw_PreContractFact
				WHERE (StatusID=10 OR StatusID=11) AND 
					(TradeYear=@Year AND TradeQuater=@Quater) AND IsRisk=1
					AND BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=@EmployeeID)
					AND ASBorLucent=@ASBorLucent
				GROUP BY ProvinceID,CATID
			UNION ALL
			SELECT ProvinceID,CATID, 'Total' AS SecureOrRisk,
				SUM(ROUND(ToBeTransformedRMB/1.17/@ExRate/1000000,4)) AS ToBeTransformed,
				SUM(ROUND(DeliveredRMB/1.17/@ExRate/1000000,4)) AS Delivered,
				SUM(ToBeTransformedCount) ToBeTransformedCount,
				SUM(DeliveredCount) DeliveredCount
			FROM dbo.vw_PreContractFact
				WHERE (StatusID=10 OR StatusID=11) AND 
					(TradeYear=@Year AND TradeQuater=@Quater)
					AND BDID IN (SELECT BDID FROM dbo.BDAssignment WHERE EmployeeID=@EmployeeID)
					AND ASBorLucent=@ASBorLucent
				GROUP BY ProvinceID,CATID
		END
	END
	ELSE
	BEGIN
		SELECT ProvinceID,CATID, 'Secure' AS SecureOrRisk,
			SUM(ROUND(ToBeTransformedRMB/1.17/@ExRate/1000000,4)) AS ToBeTransformed,
			SUM(ROUND(DeliveredRMB/1.17/@ExRate/1000000,4)) AS Delivered,
			SUM(ToBeTransformedCount) ToBeTransformedCount,
			SUM(DeliveredCount) DeliveredCount
		FROM dbo.vw_PreContractFact
			WHERE (StatusID=10 OR StatusID=11) AND 
				(TradeYear=@Year AND TradeQuater=@Quater) AND IsRisk=0
			GROUP BY ProvinceID,CATID
		UNION ALL
		SELECT ProvinceID,CATID, 'Risk' AS SecureOrRisk,
			SUM(ROUND(ToBeTransformedRMB/1.17/@ExRate/1000000,4)) AS ToBeTransformed,
			SUM(ROUND(DeliveredRMB/1.17/@ExRate/1000000,4)) AS Delivered,
			SUM(ToBeTransformedCount) ToBeTransformedCount,
			SUM(DeliveredCount) DeliveredCount
		FROM dbo.vw_PreContractFact
			WHERE (StatusID=10 OR StatusID=11) AND 
				(TradeYear=@Year AND TradeQuater=@Quater) AND IsRisk=1
			GROUP BY ProvinceID,CATID
		UNION ALL
		SELECT ProvinceID,CATID, 'Total' AS SecureOrRisk,
			SUM(ROUND(ToBeTransformedRMB/1.17/@ExRate/1000000,4)) AS ToBeTransformed,
			SUM(ROUND(DeliveredRMB/1.17/@ExRate/1000000,4)) AS Delivered,
			SUM(ToBeTransformedCount) ToBeTransformedCount,
			SUM(DeliveredCount) DeliveredCount
		FROM dbo.vw_PreContractFact
			WHERE (StatusID=10 OR StatusID=11) AND 
				(TradeYear=@Year AND TradeQuater=@Quater)
			GROUP BY ProvinceID,CATID
	END
	
	SET NOCOUNT OFF;
END

GO

/****** Object:  StoredProcedure [dbo].[sp_LoadEmployee]    Script Date: 07/03/2009 13:54:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_LoadEmployee]
	@Badge NVARCHAR(50),
	@EName NVARCHAR(50),
	@CName NVARCHAR(50),
	@Email NVARCHAR(50),
	@CILAccount NVARCHAR(50),
	@BG NVARCHAR(50),	
	@BD NVARCHAR(50),
	@Title NVARCHAR(200),
	@Phone NVARCHAR(50),
	@Company NVARCHAR(50),
	@Location NVARCHAR(50),
	@ADAccount NVARCHAR(50)
AS
--@CILAccount

IF NOT EXISTS(SELECT * FROM dbo.Employee WHERE Badge=@Badge)
BEGIN
	INSERT INTO dbo.Employee
			   ([Badge]
			   ,[EName]
			   ,[CName]
			   ,[Email]
			   ,[CILAccount]
			   ,[BD]
			   ,[BG]
			   ,[Title]
			   ,[Active]
			   ,[UpdateDate]
			   ,[Phone]
			   ,[Company]
			   ,[Location]
			   ,[ADAccount])
		 VALUES
			   (@Badge
			   ,@EName
			   ,@CName
			   ,@Email
			   ,@CILAccount
			   ,@BD
			   ,@BG
			   ,@Title
			   ,0
			   ,GETDATE()
			   ,@Phone
			   ,@Company
			   ,@Location
			   ,@ADAccount)

END
ELSE
	UPDATE dbo.Employee SET 
			   EName=@EName
			   ,CName=@CName
			   ,Email=@Email
			   ,CILAccount=@CILAccount
			   ,BD=@BD
			   ,BG=@BG
			   ,Title=@Title
			   ,UpdateDate=GETDATE()
			   ,Phone=@Phone
			   ,Company=@Company
			   ,Location=@Location
			   ,ADAccount=@ADAccount
	WHERE Badge=@Badge

GO

/****** Object:  StoredProcedure [dbo].[sp_LoadHistData]    Script Date: 07/03/2009 13:54:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_LoadHistData]
	@StatusID INT,
	@TypeID INT,
	@PreContractNo NVARCHAR(20),
	@ProductName NVARCHAR(500),
	@BDCode NVARCHAR(10),
	@CEWPromised MONEY,
	@OrionPrice MONEY,
	@SalesPrice MONEY,	
	@SignDue SMALLDATETIME,
	@SignActual SMALLDATETIME,	
	@AdvancedContract NVARCHAR(500),
	@CATID INT,
	@PromisedDate SMALLDATETIME,
	@ContractName NVARCHAR(200),
	@CustomerName NVARCHAR(200),
	@ERPReceived SMALLDATETIME,
	@OrionNo NVARCHAR(200),
	@Delivered BIT,
	@ProvinceID INT,
	@CurrentSituation NVARCHAR(1000),
	@TradeMonth SMALLDATETIME,
	@ModifiedDate SMALLDATETIME,
	@BlackDate SMALLDATETIME,
	@ACRNo NVARCHAR(20),
	@ASBorLucent BIT

AS
DECLARE @ProductID INT
	DECLARE @BDID INT	
	DECLARE @DeliveryPercent DECIMAL(5,2) 
	DECLARE @IsRisk BIT

	--GET DeliveryPercent
	IF @Delivered=1 
		SET @DeliveryPercent=100.0
	ELSE
		SET @DeliveryPercent=0.000



	SET @IsRisk=0

	--Get ProductID
	--PRINT @ProductName
	SET @ProductID=0
	SELECT @ProductID=ProductID FROM dbo.Product WHERE ProductName=@ProductName

	IF @ProductID=0
	BEGIN
		INSERT INTO dbo.Product(ProductName) VALUES(@ProductName)
		SET @ProductID=@@IDENTITY
	END
	--PRINT @ProductID

	 


	--GET BD ID
	SET @BDID=0
	SELECT @BDID=BDID FROM dbo.BD WHERE BDCode=@BDCode
	IF @BDID=0
	BEGIN
		INSERT INTO dbo.BD(BDCode,SortNumber,CreateDate,Active,BGID) VALUES(@BDCode,0,GETDATE(),1,1)
		SET @BDID=@@IDENTITY
	END	
	
	IF NOT EXISTS(SELECT PreContractID FROM [dbo].[PreContractInfo] 
		WHERE BDID=@BDID AND PreContractNo=@PreContractNo)
			
		INSERT INTO [dbo].[PreContractInfo]
			   ([PreContractNo]
			   ,[StatusID]
			   ,[TypeID]
			   ,[ProductID]
			   ,[PromisedDate]
			   ,[CEWPromised]
			   ,[OrionPrice]
			   ,[SalePrice]
			   ,[SignDue]
			   ,[SignActual]
			   ,[ContractName]
			   ,[ERPReceived]
			   ,[OrionNo]
			   ,[Delivered]
			   ,[CurrentSituation]
			   ,[BlackDate]
			   ,[TradeMonth]
			   ,[ModifiedDate]
			   ,[ACRNo]
			   ,[ASBorLucent]			
			   ,[ProvinceID]
			   ,[CATID]
			   ,[BDID]
			   ,[CustomerName]
			   ,[DeliveryPercent]
			   ,[AdvancedContract]
			   ,[IsRisk])
		 VALUES
			   (@PreContractNo
			   ,@StatusID
			   ,@TypeID
			   ,@ProductID
			   ,@PromisedDate
			   ,@CEWPromised
			   ,@OrionPrice
			   ,@SalesPrice
			   ,@SignDue
			   ,@SignActual
			   ,@ContractName
			   ,@ERPReceived
			   ,@OrionNo
			   ,@Delivered
			   ,@CurrentSituation
			   ,@BlackDate
			   ,@TradeMonth
			   ,@ModifiedDate
			   ,@ACRNo
			   ,@ASBorLucent
			   ,@ProvinceID
			   ,@CATID
			   ,@BDID
			   ,@CustomerName
			   ,@DeliveryPercent
			   ,@AdvancedContract
			   ,@IsRisk)

	IF @@ERROR <>0
		RETURN -1
	ELSE	
		RETURN 0

GO

/****** Object:  StoredProcedure [dbo].[sp_SaveReportHistory]    Script Date: 07/03/2009 13:54:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Joan.lu>
-- Create date: <2008-09-25>
-- Description:	<For Save a history report, Add or Update>
-- =============================================
CREATE PROCEDURE [dbo].[sp_SaveReportHistory]
@ReportDate SMALLDATETIME,
@ReportType NVARCHAR(50),
@ReportContent NTEXT
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @ReportID INT

	IF EXISTS (SELECT ReportID FROM dbo.ReportHistory WHERE ReportDate=@ReportDate AND ReportType=@ReportType)
	BEGIN
		UPDATE dbo.ReportHistory 
			SET ReportContent=@ReportContent
		WHERE ReportDate=@ReportDate AND ReportType=@ReportType

		SELECT ReportID,ReportDate FROM dbo.ReportHistory WHERE ReportDate=@ReportDate AND ReportType=@ReportType
	END
	ELSE
	BEGIN
		INSERT INTO dbo.ReportHistory(ReportDate,ReportType,ReportContent) 
			VALUES(@ReportDate,@ReportType,@ReportContent)
		SET @ReportID=@@IDENTITY
		SELECT ReportID,ReportDate FROM dbo.ReportHistory WHERE ReportID=@ReportID	
	END
	
		
	SET NOCOUNT OFF;
END

GO

/****** Object:  StoredProcedure [dbo].[sp_UpdateTradeMonth]    Script Date: 07/03/2009 13:54:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Joan.lu>
-- Create date: <2008-09-28>
-- Description:	<For UPDATE TradeMonth>
-- =============================================
CREATE PROCEDURE [dbo].[sp_UpdateTradeMonth]
	@CloseDateID INT 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @StartDate SMALLDATETIME
	DECLARE @EndDate SMALLDATETIME
	DECLARE @Year INT
	DECLARE @Month INT
		
	
	IF EXISTS(SELECT CloseDateID FROM dbo.CloseDate WHERE CloseDateID=@CloseDateID)
	BEGIN
		--Update trademonth where sign due is between start date and end date
		SELECT @StartDate=StartDate, @EndDate=EndDate, @Year=[Year],@Month=[Month]
			FROM dbo.CloseDate WHERE CloseDateID=@CloseDateID
		UPDATE dbo.PreContractInfo 
			SET TradeMonth=CAST((CAST(@Year AS CHAR(4)) + '-' + CAST(@Month AS VARCHAR(2)) + '-01') AS SMALLDATETIME) 
			WHERE SignDue BETWEEN @StartDate AND @EndDate
		
		--if enddate changes, the next month start date will change,
		--Update trademonth where sign due is between next month start date and end date
		IF @Month=12
			SELECT @StartDate=StartDate, @EndDate=EndDate, @Year=[Year],@Month=[Month]
				FROM dbo.CloseDate WHERE [Year]=@Year+1 AND [Month]=1
		ELSE
			SELECT @StartDate=StartDate, @EndDate=EndDate, @Year=[Year],@Month=[Month]
				FROM dbo.CloseDate WHERE [Year]=@Year AND [Month]=@Month+1	
	
		UPDATE dbo.PreContractInfo 
			SET TradeMonth=CAST((CAST(@Year AS CHAR(4)) + '-' + CAST(@Month AS VARCHAR(2)) + '-01') AS SMALLDATETIME) 
			WHERE SignDue BETWEEN @StartDate AND @EndDate		
		
	END

	SET NOCOUNT OFF;
END

GO

/****** Object:  StoredProcedure [dbo].[sp_UpdateTradeMonthAll]    Script Date: 07/03/2009 13:54:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Edwin.fan>
-- Create date: <2008-11-03>
-- Description:	<For UPDATE TradeMonth>
-- =============================================
CREATE PROCEDURE [dbo].[sp_UpdateTradeMonthAll]
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	--UPDATE status to 11 (半转正) for pre-contracts with advanced contract but status is pending
	UPDATE dbo.PreContractInfo SET StatusID=11
		WHERE StatusID=10 AND (AdvancedContract<>'' AND AdvancedContract IS NOT NULL)
	


	DECLARE @StartDate SMALLDATETIME
	DECLARE @EndDate SMALLDATETIME
	DECLARE @Year INT
	DECLARE @Month INT
    DECLARE @CloseDateID INT
	DECLARE @count INT
	DECLARE @i INT
    
    DECLARE cur1 CURSOR FOR SELECT CloseDateID FROM CloseDate;

    SET @i =0
    SELECT @count = COUNT(CloseDateID) FROM CloseDate
   
    OPEN cur1
  
	WHILE @i < @count
	BEGIN
        FETCH cur1 INTO @CloseDateID
		
        SELECT @StartDate=StartDate, @EndDate=EndDate, @Year=[Year],@Month=[Month]
			FROM dbo.CloseDate WHERE CloseDateID=@CloseDateID
		UPDATE dbo.PreContractInfo 
			SET TradeMonth=CAST((CAST(@Year AS CHAR(4)) + '-' + CAST(@Month AS VARCHAR(2)) + '-01') AS SMALLDATETIME) 
			WHERE SignDue BETWEEN @StartDate AND @EndDate
		
		--if enddate changes, the next month start date will change,
		--Update trademonth where sign due is between next month start date and end date
		IF @Month=12
			SELECT @StartDate=StartDate, @EndDate=EndDate, @Year=[Year],@Month=[Month]
				FROM dbo.CloseDate WHERE [Year]=@Year+1 AND [Month]=1
		ELSE
			SELECT @StartDate=StartDate, @EndDate=EndDate, @Year=[Year],@Month=[Month]
				FROM dbo.CloseDate WHERE [Year]=@Year AND [Month]=@Month+1	
	
		UPDATE dbo.PreContractInfo 
			SET TradeMonth=CAST((CAST(@Year AS CHAR(4)) + '-' + CAST(@Month AS VARCHAR(2)) + '-01') AS SMALLDATETIME) 
			WHERE SignDue BETWEEN @StartDate AND @EndDate			
		
        set @i = @i + 1
	END

    CLOSE cur1
    
	SET NOCOUNT OFF;
END

GO


